In SQL, SET operators allow you to combine the results of two or more SELECT statements. The three primary SET operators are UNION, INTERSECT, and EXCEPT. Here's a brief overview of each, along with examples of how to use them.
1. UNION
- Purpose: Combines the results of two or more SELECT statements, removing duplicate rows from the final result set.
- Syntax:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
- Example:
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;
This query retrieves the unique first and last names of all employees and contractors.
2. INTERSECT
- Purpose: Returns only the rows that are present in both SELECT statement results.
- Syntax:
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
- Example:
SELECT product_id FROM sales_2022
INTERSECT
SELECT product_id FROM sales_2023;
This query retrieves the product IDs that were sold in both 2022 and 2023.
3. EXCEPT (or MINUS in some databases)
- Purpose: Returns the rows from the first SELECT statement that are not present in the second SELECT statement.
- Syntax:
SELECT column1, column2, ...
FROM table1
EXCEPT
SELECT column1, column2, ...
FROM table2;
- Example:
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
This query retrieves customer IDs that have not placed any orders.
Important Notes
- Column Matching: The number of columns and their data types in the SELECT statements must match for the SET operators to work correctly.
- Performance: Each SET operator can affect performance, especially with large result sets, so consider optimizations as needed.
- Ordering Results: If you want to sort the final result set, you must do so after the entire combined set has been completed. For instance:
SELECT first_name FROM employees
UNION
SELECT first_name FROM contractors
ORDER BY first_name;
This sorts the final combined result by the first_name column.
By using these operators, you can perform complex queries to analyze and manipulate your data across multiple tables effectively.