UNION, INTERSECT, EXCEPT Operations

Difficulty: Intermediate
Generated on: 2025-07-10 02:25:01
Category: SQL Cheatsheet for Database Development

SQL Set Operations Cheatsheet: UNION, INTERSECT, EXCEPT

This cheatsheet provides a practical guide to UNION, INTERSECT, and EXCEPT operations in SQL. It covers syntax, use cases, best practices, and common pitfalls to help you effectively use these powerful features in your database development.

1. Quick Overview

Operation	Description	When to Use
`UNION`	Combines the result sets of two or more `SELECT` statements into a single result set, removing duplicates (by default).	Merging data from multiple tables with similar structures; consolidating data from different sources.
`UNION ALL`	Combines the result sets of two or more `SELECT` statements into a single result set, including duplicates.	Merging data where duplicates are acceptable or expected; when performance is critical and duplicate removal is unnecessary.
`INTERSECT`	Returns the common rows found in the result sets of two or more `SELECT` statements.	Finding common elements between datasets; identifying shared characteristics across different tables.
`EXCEPT`	Returns the rows from the first `SELECT` statement that are not found in the second `SELECT` statement.	Identifying differences between datasets; finding records unique to one table compared to another.

2. Syntax

-- UNION (Removes duplicates)
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

-- UNION ALL (Includes duplicates)
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

-- INTERSECT
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

-- EXCEPT (Also known as MINUS in some databases, e.g., Oracle)
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Important Notes:

The number and order of columns must be the same in all SELECT statements.
The data types of the corresponding columns must be compatible.
INTERSECT and EXCEPT might have different syntax or availability depending on the database system (see Database Variations section).

3. Common Use Cases

Merging Customer Data: Combining customer information from different regions or databases into a single view.
Finding Common Products: Identifying products sold in multiple stores or categories.
Identifying Inactive Users: Finding users who are present in a user table but not in an active login table.
Creating Consolidated Reports: Combining data from multiple reporting tables to generate a comprehensive report.
Data Migration Validation: Comparing data between the source and destination databases after a migration to identify discrepancies.

4. Best Practices

Use UNION ALL when possible: UNION ALL is generally faster than UNION because it doesn’t perform duplicate removal. Only use UNION if you specifically need to remove duplicates.
Use appropriate indexes: Ensure that the columns used in the SELECT statements have appropriate indexes to improve query performance.
Use WHERE clauses to filter data: Filter the data in each SELECT statement before combining the results. This can significantly reduce the amount of data processed and improve performance.
Consider using temporary tables: For complex operations, consider using temporary tables to store intermediate results. This can improve readability and performance.
Test and optimize: Always test your queries with realistic data and use query execution plans to identify potential performance bottlenecks.
Explicit Column Names: Always explicitly specify column names for clarity and maintainability, especially when dealing with complex queries.
Use Aliases: Use aliases for tables and columns to improve readability, especially when joining multiple tables.

5. Examples

Let’s assume we have two tables: employees and contractors.

employees Table:

employee_id	employee_name	department
1	John Doe	Sales
2	Jane Smith	Marketing
3	Peter Jones	IT
4	Alice Brown	Sales

contractors Table:

contractor_id	contractor_name	department
101	John Doe	Sales
102	David Lee	IT
103	Alice Brown	Sales
104	Susan Wilson	HR

Example 1: UNION - Combining Employees and Contractors (Removing Duplicates)

-- Get a list of all names and departments from both tables, removing duplicates
SELECT employee_name AS name, department FROM employees
UNION
SELECT contractor_name AS name, department FROM contractors;

-- Output:
-- | name          | department |
-- | :------------ | :--------- |
-- | John Doe      | Sales      |
-- | Jane Smith    | Marketing  |
-- | Peter Jones   | IT         |
-- | Alice Brown   | Sales      |
-- | David Lee       | IT         |
-- | Susan Wilson    | HR         |

Example 2: UNION ALL - Combining Employees and Contractors (Including Duplicates)

-- Get a list of all names and departments from both tables, including duplicates
SELECT employee_name AS name, department FROM employees
UNION ALL
SELECT contractor_name AS name, department FROM contractors;

-- Output:
-- | name          | department |
-- | :------------ | :--------- |
-- | John Doe      | Sales      |
-- | Jane Smith    | Marketing  |
-- | Peter Jones   | IT         |
-- | Alice Brown   | Sales      |
-- | John Doe      | Sales      |
-- | David Lee       | IT         |
-- | Alice Brown     | Sales      |
-- | Susan Wilson    | HR         |

Example 3: INTERSECT - Finding Employees and Contractors with the Same Name and Department

-- Find employees and contractors who share the same name and department
SELECT employee_name AS name, department FROM employees
INTERSECT
SELECT contractor_name AS name, department FROM contractors;

-- Output:
-- | name      | department |
-- | :-------- | :--------- |
-- | John Doe  | Sales      |
-- | Alice Brown | Sales      |

Example 4: EXCEPT - Finding Employees Not Listed as Contractors

-- Find employees who are not listed as contractors
SELECT employee_name AS name, department FROM employees
EXCEPT
SELECT contractor_name AS name, department FROM contractors;

-- Output:
-- | name       | department |
-- | :--------- | :--------- |
-- | Jane Smith | Marketing  |
-- | Peter Jones| IT         |

Example 5: Combining WHERE clause with UNION

-- Find all employees in Sales or Marketing departments, combined with
-- contractors in IT department

SELECT employee_name AS name, department FROM employees WHERE department IN ('Sales', 'Marketing')
UNION
SELECT contractor_name AS name, department FROM contractors WHERE department = 'IT';

-- Output:
-- | name          | department |
-- | :------------ | :--------- |
-- | John Doe      | Sales      |
-- | Jane Smith    | Marketing  |
-- | Alice Brown   | Sales      |
-- | David Lee       | IT         |

6. Common Pitfalls

Incorrect Column Order or Data Types: Ensure that the columns in the SELECT statements are in the same order and have compatible data types. Mismatched data types will often lead to errors or unexpected results.
Missing ALIAS: When column names are different between tables, use AS to create aliases for consistent column names in the result set.
Performance Issues with Large Datasets: UNION operations, especially on large datasets, can be resource-intensive. Use indexes and WHERE clauses to optimize performance. Consider breaking down large operations into smaller, more manageable chunks.
Incorrect Use of UNION vs. UNION ALL: Understand the difference between UNION and UNION ALL. Using UNION when you don’t need to remove duplicates can significantly impact performance.
Database-Specific Syntax: Be aware of database-specific syntax variations for INTERSECT and EXCEPT (see Database Variations section).
Null Values: Be mindful of how null values are handled by these operators. In some cases, null values might prevent rows from being included in the result set. Consider using COALESCE or IS NULL / IS NOT NULL to handle null values appropriately.

Troubleshooting Tips:

Check for data type mismatches: Use DESCRIBE or INFORMATION_SCHEMA.COLUMNS to verify the data types of the columns being combined.
Simplify the query: Start with a simple query and gradually add complexity, testing each step to identify the source of the problem.
Use EXPLAIN or EXPLAIN PLAN: Examine the query execution plan to identify potential performance bottlenecks.
Test with sample data: Create a small sample dataset to test your query and verify that it produces the expected results.

7. Database Variations

Database	`INTERSECT`	`EXCEPT`	Notes
MySQL	Not Supported	Not Supported	MySQL does not have direct support for `INTERSECT` and `EXCEPT`. You can achieve similar results using subqueries, `JOIN` operations, or temporary tables.
PostgreSQL	Supported	`EXCEPT`	Standard SQL syntax. `EXCEPT ALL` is also supported to retain duplicates.
SQL Server	Supported	`EXCEPT`	Standard SQL syntax.
Oracle	Supported	`MINUS`	Oracle uses `MINUS` instead of `EXCEPT`.
SQLite	Supported	`EXCEPT`	Standard SQL syntax.

MySQL Workarounds (for INTERSECT and EXCEPT):

INTERSECT Equivalent in MySQL:

-- Using INNER JOIN
SELECT t1.column1, t1.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;

-- Using WHERE EXISTS
SELECT column1, column2
FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column1 AND table1.column2 = table2.column2);

EXCEPT Equivalent in MySQL:

-- Using LEFT JOIN and WHERE IS NULL
SELECT t1.column1, t1.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2
WHERE t2.column1 IS NULL;

-- Using NOT EXISTS
SELECT column1, column2
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column1 AND table1.column2 = table2.column2);

This cheatsheet provides a comprehensive overview of UNION, INTERSECT, and EXCEPT operations in SQL, covering syntax, use cases, best practices, common pitfalls, and database-specific considerations. Use it as a quick reference guide during development and to help you write efficient and effective SQL queries.