SQL set operations are used to combine or manipulate data from multiple tables or result sets. There are three primary set operations in SQL: UNION, INTERSECT, and EXCEPT (or MINUS in some database systems). Let’s discuss each operation in detail:
- UNION: The UNION operation combines the results of two or more SELECT statements into a single result set. It removes duplicate rows from the result set by default. The syntax for UNION is as follows:
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
Note that the number and data types of columns must match in the SELECT statements involved in the UNION operation.
2. INTERSECT: The INTERSECT operation returns the common rows that exist in the result sets of two or more SELECT statements. It, too, removes duplicate rows from the result set. The syntax for INTERSECT is as follows:
SELECT column1, column2, ... FROM table1 INTERSECT SELECT column1, column2, ... FROM table2;
Like the UNION operation, the number and data types of columns must match in the SELECT statements.
3. EXCEPT (or MINUS): The EXCEPT operation returns the distinct rows present in the result set of the first SELECT statement but not in the result set of the second SELECT statement. It removes duplicate rows from the result set. The syntax for EXCEPT is as follows:
SELECT column1, column2, ... FROM table1 EXCEPT SELECT column1, column2, ... FROM table2;
Again, the number and data types of columns must match in the SELECT statements involved.
It’s important to note that the set operations require that the columns being compared have compatible data types. Also, the column names of the result set are determined by the column names in the first SELECT statement of the operation.
These set operations can be useful when you want to combine data from different tables, find common elements, or perform data comparisons in SQL.