In SQL, set operations allow you to combine the results of two or more queries. There are three primary set operations: UNION, INTERSECT, and EXCEPT (or MINUS, depending on the database system). These operations are typically performed on the result sets of two or more SELECT statements.
The UNION operator is used to combine the results of two or more SELECT statements, removing duplicate rows from the final result set.
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;
Example:
SELECT product_name FROM category1
UNION
SELECT product_name FROM category2;
The INTERSECT operator returns the common rows that appear in the result sets of two SELECT statements.
SELECT column1, column2
FROM table1
INTERSECT
SELECT column1, column2
FROM table2;
Example:
SELECT employee_id FROM department1
INTERSECT
SELECT employee_id FROM department2;
The EXCEPT (or MINUS in some database systems) operator 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.
SELECT column1, column2
FROM table1
EXCEPT
SELECT column1, column2
FROM table2;
Example:
SELECT product_name FROM category1
EXCEPT
SELECT product_name FROM category2;
It's important to note that for these set operations to work, the SELECT statements involved must have the same number of columns, and the corresponding columns must have compatible data types.
If you want to include duplicate rows in the results, you can use UNION ALL, INTERSECT ALL, and EXCEPT ALL. These variations include all rows, even if they are duplicates.
SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;