SQL Set Operators: Syntax, Usage, and Examples

SQL set operators allow you to combine results from multiple queries into a single output. These operators work with SELECT statements that have the same number of columns and compatible data types. Common SQL set operations include UNION, UNION ALL, INTERSECT, and EXCEPT (or MINUS in some databases). Understanding how to use set operators in SQL helps in data analysis and reporting.

How to Use SQL Set Operators

Set operators follow a simple syntax where multiple SELECT statements are combined into a single result.


                  
                    sql
                    
                  
                  
                    SELECT column1 FROM table1
SET_OPERATOR
SELECT column1 FROM table2;

Each SELECT statement must have:

The same number of columns
Matching data types in corresponding columns
The same order of columns

Common SQL Set Operators

UNION

Combines unique rows from two queries.


                  
                    sql
                    
                  
                  
                    SELECT customer_id FROM customers_2023
UNION
SELECT customer_id FROM customers_2024;

UNION ALL

Combines all rows, including duplicates.


                  
                    sql
                    
                  
                  
                    SELECT customer_id FROM customers_2023
UNION ALL
SELECT customer_id FROM customers_2024;

INTERSECT

Returns rows that exist in both queries.


                  
                    sql
                    
                  
                  
                    SELECT employee_id FROM department_A
INTERSECT
SELECT employee_id FROM department_B;

EXCEPT (or MINUS)

Returns rows from the first query that are not in the second. EXCEPT is used in PostgreSQL and SQL Server, while MINUS is used in Oracle.


                  
                    sql
                    
                  
                  
                    SELECT product_id FROM store_A
EXCEPT
SELECT product_id FROM store_B;

When to Use SQL Set Operators

Merging Data from Multiple Tables

If you store customer data in different regions, UNION helps combine those lists into one without duplicates.

Finding Common Records

When you need to identify employees working in multiple departments, INTERSECT ensures you only retrieve shared records.

Analyzing Differences in Datasets

To check which products were sold last year but not this year, EXCEPT helps spot missing records.

Removing Duplicates from Combined Results

Using UNION ensures duplicate rows are removed when merging datasets, improving data clarity.

Examples of SQL Set Operators

Using UNION to Merge Customer Lists


                  
                    sql
                    
                  
                  
                    SELECT first_name, last_name, email FROM customers_USA
UNION
SELECT first_name, last_name, email FROM customers_Europe;

This query returns a combined list of unique customers from both regions.

Using UNION ALL to Include Duplicates


                  
                    sql
                    
                  
                  
                    SELECT category FROM electronics
UNION ALL
SELECT category FROM furniture;

All categories from both tables appear, even if duplicates exist.

Using INTERSECT to Find Common Employees


                  
                    sql
                    
                  
                  
                    SELECT employee_id FROM project_A
INTERSECT
SELECT employee_id FROM project_B;

Only employees assigned to both projects are returned.

Using EXCEPT to Find Missing Customers


                  
                    sql
                    
                  
                  
                    SELECT customer_id FROM customers_2023
EXCEPT
SELECT customer_id FROM customers_2024;

This finds customers who were active in 2023 but not in 2024.

Using MINUS in Oracle to Find Products Not Restocked


                  
                    sql
                    
                  
                  
                    SELECT product_id FROM inventory_2023
MINUS
SELECT product_id FROM inventory_2024;

Products from 2023 that were not restocked in 2024 appear in the result.

Learn More About SQL Set Operators

Sorting Results with ORDER BY

When using set operators, ORDER BY applies to the final result set.


                  
                    sql
                    
                  
                  
                    SELECT name FROM suppliers
UNION
SELECT name FROM customers
ORDER BY name;

Using Set Operators with Aggregates

Set operators can be combined with aggregate functions like COUNT() or SUM().


                  
                    sql
                    
                  
                  
                    SELECT department, COUNT(*) FROM employees_2023
UNION
SELECT department, COUNT(*) FROM employees_2024;

Optimizing Set Operations

Use UNION ALL when duplicates are acceptable, as UNION requires extra processing.
Ensure indexes exist on filtered columns to speed up execution.
Avoid unnecessary ORDER BY when performance is a concern.

SQL set operators simplify combining, filtering, and analyzing data across multiple queries. UNION merges results, INTERSECT finds common data, and EXCEPT or MINUS identifies differences.