1. What is MERGE?
The MERGE statement in SQL is a clause that can handle insert, update, and deletes all in a single transaction without having to write sepatate logic for each of these. Using the MERGE statement in SQL gives us better flexibility in customizing our complex SQL scripts and also enhances the readability of our scripts.
2. Kinds of Merge clause
We can join data from more than two tables by stacking JOIN clause.
2.1 INNER JOIN
The INNER JOIN clause selects records that have matching values in both tables.
SELECT [alias_table_name1].[col_name1], [alias_table_name2].[col_name2], ...
FROM [table_name1] AS [alias_table_name1]
INNER JOIN [table_name2] AS [alais_table_name2] ON [alias_table_name1].[key1] = [alias_table_name2].[key2]
2.2 LEFT JOIN
The LEFT JOIN clause returns all records from the left table, and the matching records from the right table. The result is 0 records from the right side, if there is no match.
SELECT [alias_table_name1].[col_name1], [alias_table_name2].[col_name2], ...
FROM [table_name1] AS [alias_table_name1]
LEFT JOIN [table_name2] AS [alais_table_name2] ON [alias_table_name1].[key1] = [alias_table_name2].[key2]
2.3 RIGHT JOIN
The RIGHT JOIN clause returns all records from the right table, and the matching records from the left table. The resutl i 0 records from the left side, if there is no match.
SELECT [alias_table_name1].[col_name1], [alias_table_name2].[col_name2], ...
FROM [table_name1] AS [alias_table_name1]
RIGHT JOIN [table_name2] AS [alais_table_name2] ON [alias_table_name1].[key1] = [alias_table_name2].[key2]
2.4 FULL OUTER JOIN
The FULL OUTER JOIN clause returns all records when there is a match in left or right table records.
SELECT [alias_table_name1].[col_name1], [alias_table_name2].[col_name2], ...
FROM [table_name1] AS [alias_table_name1]
FULL OUTER JOIN [table_name2] AS [alais_table_name2] ON [alias_table_name1].[key1] = [alias_table_name2].[key2]
2.5 UNION
The UNION clause selects rows that occurs in one or more SELECT clause. This clause can stack duplicated rows among tables.
[select statement]
UNION
[select statement]
2.6 INTERSECT
The INTERSECT clause selects rows that occurs both SELECT statement. This clause stacks rows without duplicated rows.
[select statement]
INTERSECT
[select statement]
2.7 EXCEPT
The EXCEPT clause selects rows that occurs in the first SELECT statement but not the second SELECT statement.
[select statement]
EXCEPT
[select statement]
Source from :
'DB > SQLite' 카테고리의 다른 글
| [sqlite3] Creating a sqlite database from CSV file (0) | 2022.09.24 |
|---|---|
| [Syntax] Data Definition Language (0) | 2022.09.24 |
| [Syntax] Writing Efficient Query (0) | 2022.09.24 |
| [Syntax] Data Manipulation Language (0) | 2022.09.23 |
| [Theorem] What is SQLite? (0) | 2022.09.23 |