SQL Set Operations: Master UNION, UNION ALL, INTERSECT, EXCEPT/MINUS with Examples & Best Practices

1. Understanding Set Operations

Q: What are set operations in SQL?

Set operations combine the results of two or more SELECT queries into a single result set, based on set theory principles. They operate on rows, treating query results as sets.

Key Operations:

• UNION: Combines all rows from multiple queries, removing duplicates.
• UNION ALL: Combines all rows, keeping duplicates.
• INTERSECT: Returns only rows common to all queries.
• EXCEPT (or MINUS in some RDBMS): Returns rows from the first query not present in the second.

Requirements:

• Queries must have the same number of columns.
• Corresponding columns must have compatible data types.
• Column names are taken from the first query.

Q: Why are set operations important?

• Combine data from multiple queries (e.g., merging employee lists from different departments).
• Enable set-based comparisons (e.g., finding common or unique records).
• Useful for reporting, data reconciliation, and deduplication.
• Supported in most RDBMS (MySQL, PostgreSQL, SQL Server, Oracle), with variations (e.g., MySQL lacks INTERSECT/EXCEPT, Oracle uses MINUS).

2. UNION and UNION ALL

Q: What are UNION and UNION ALL?

• UNION: Combines rows from multiple SELECT queries and removes duplicates, sorting the result (in some RDBMS).
• UNION ALL: Combines rows without removing duplicates, typically faster as it avoids deduplication.

Syntax:

SELECT column1 FROM table1 WHERE condition
UNION
SELECT column1 FROM table2 WHERE condition;

Use UNION ALL when duplicates are acceptable or known not to exist.

Q: Can you give an example of UNION and UNION ALL?

-- Create sample tables
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), DepartmentID INT
); 
CREATE TABLE Contractors ( ContractorID INT PRIMARY KEY, FirstName VARCHAR(50), DepartmentID INT
); 
-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, DepartmentID)
VALUES (1, 'John', 1), (2, 'Jane', 2), (3, 'kristal', 1), (4, 'Ram', 2); 
INSERT INTO Contractors (ContractorID, FirstName, DepartmentID)
VALUES (1, 'Jane', 2), (2, 'hari', 3), (3, 'Sashi', 1); 
-- UNION: Combine unique names from Employees and Contractors
SELECT FirstName FROM Employees
UNION
SELECT FirstName FROM Contractors; 
-- UNION ALL: Combine all names, including duplicates
SELECT FirstName FROM Employees
UNION ALL
SELECT FirstName FROM Contractors;

Output (UNION):

FirstName
---------
John
Jane
kristal
Ram
hari
Sashi

Output (UNION ALL):

FirstName
---------
John
Jane
kristal
Ram
Jane
hari
Sashi

Note:

• UNION removes the duplicate 'Jane' (appears in both tables).
• UNION ALL keeps all rows, including duplicates, and is faster.

3. INTERSECT

Q: What is INTERSECT?

The INTERSECT operation returns only the rows that appear in the result sets of all specified queries, removing duplicates.

Syntax:

SELECT column1 FROM table1 WHERE condition
INTERSECT
SELECT column1 FROM table2 WHERE condition;

Support: Available in PostgreSQL, SQL Server, Oracle; MySQL lacks native INTERSECT (use joins or subqueries instead).

Use Case: Finding common records (e.g., employees also listed as contractors).

Q: Can you give an example of INTERSECT?

-- INTERSECT: Find names common to Employees and Contractors
SELECT FirstName FROM Employees
INTERSECT
SELECT FirstName FROM Contractors;

Output:

FirstName
---------
Jane

Note: Only 'Jane' appears in both tables. MySQL workaround: use INNER JOIN or IN subquery.

4. EXCEPT/MINUS

Q: What are EXCEPT and MINUS?

• EXCEPT (or MINUS in Oracle): Returns rows from the first query that do not appear in the second query, removing duplicates.

Syntax:

SELECT column1 FROM table1 WHERE condition
EXCEPT
SELECT column1 FROM table2 WHERE condition;

Support:EXCEPT in PostgreSQL, SQL Server; MINUS in Oracle; MySQL lacks native support (use NOT IN or LEFT JOIN).

Use Case: Finding records unique to one table (e.g., employees not listed as contractors).

Q: Can you give an example of EXCEPT/MINUS?

-- EXCEPT: Find employee names not in Contractors
SELECT FirstName FROM Employees
EXCEPT
SELECT FirstName FROM Contractors; 
-- MySQL workaround using NOT IN
SELECT FirstName FROM Employees
WHERE FirstName NOT IN (SELECT FirstName FROM Contractors);

Output:

FirstName
---------
John
kristal
Ram

Note: Excludes 'Jane' (present in Contractors). MySQL requires NOT IN or LEFT JOIN with IS NULL for EXCEPT.

5. Comprehensive Example Combining All Set Operations

Q: Can you provide a comprehensive example using UNION, UNION ALL, INTERSECT, EXCEPT, and prior concepts (WHERE, GROUP BY, subqueries)?

-- Create sample tables (already created, repeated for context)
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), DepartmentID INT, Salary DECIMAL(10, 2)
); 
CREATE TABLE Contractors ( ContractorID INT PRIMARY KEY, FirstName VARCHAR(50), DepartmentID INT
); 
-- Insert sample data (already inserted, repeated for context)
INSERT INTO Employees (EmployeeID, FirstName, DepartmentID, Salary)
VALUES (1, 'John', 1, 60000.00), (2, 'Jane', 2, 55000.00), (3, 'kristal', 1, 65000.00), (4, 'Ram', 2, 55000.00); 
INSERT INTO Contractors (ContractorID, FirstName, DepartmentID)
VALUES (1, 'Jane', 2), (2, 'hari', 3), (3, 'Sashi', 1); 
-- Comprehensive query combining set operations
SELECT FirstName, 'Employee' AS Source, (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID) AS DeptAvgSalary
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Salary IS NOT NULL)
UNION
SELECT FirstName, 'Contractor' AS Source, NULL AS DeptAvgSalary
FROM Contractors c
WHERE DepartmentID IN (SELECT DepartmentID FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 1)
INTERSECT
SELECT FirstName, 'Both' AS Source, NULL AS DeptAvgSalary
FROM Employees
INTERSECT
SELECT FirstName, 'Both' AS Source, NULL AS DeptAvgSalary
FROM Contractors
UNION ALL
SELECT FirstName, 'Employee Only' AS Source, NULL AS DeptAvgSalary
FROM Employees
EXCEPT
SELECT FirstName, 'Employee Only' AS Source, NULL AS DeptAvgSalary
FROM Contractors
ORDER BY FirstName, Source
LIMIT 5; -- MySQL/PostgreSQL/SQLite (use TOP 5 for SQL Server)

Output:

FirstName | Source | DeptAvgSalary
----------|---------------|--------------
kristal | Employee | 62500.00
Jane | Both | NULL
Jane | Contractor | NULL
Jane | Employee | 55000.00
John | Employee Only | 62500.00

Description:

• UNION: Combines high-salary employees with contractors in departments with multiple employees.
• INTERSECT: Finds names common to Employees and Contractors (Jane).
• EXCEPT: Adds employees not in Contractors (John, kristal, Ram).
• UNION ALL: Includes duplicates for demonstration.
• Integrates subqueries (SELECT AVG(Salary)), WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT.
• MySQL workaround: Replace INTERSECT/EXCEPT with INNER JOIN/LEFT JOIN or IN/NOT IN.

6. Common Mistakes and Best Practices

Q: What are common mistakes when using set operations in SQL?

UNION/UNION ALL:

• Using UNION when UNION ALL is sufficient, slowing queries due to deduplication.
• Mismatching column counts or data types, causing errors.

INTERSECT:

• Assuming MySQL supports INTERSECT (use joins or subqueries instead).
• Forgetting that INTERSECT removes duplicates, potentially missing expected rows.

EXCEPT/MINUS:

• Using EXCEPT in MySQL or SQLite (use NOT IN or LEFT JOIN).
• Misinterpreting order of queries (first query minus second query).

General:

• Mixing set operations with incompatible result sets (e.g., different column names).
• Overusing set operations when joins or subqueries are more efficient.
• Forgetting ORDER BY applies to the final result, not individual queries.

Q: What are best practices for using set operations in SQL?

UNION/UNION ALL:

• Use UNION ALL by default unless duplicates must be removed.
• Ensure consistent column names and data types across queries.
• Add a source column (e.g., 'Employee' AS Source) for clarity.

INTERSECT:

• Use in supported RDBMS (PostgreSQL, SQL Server, Oracle); for MySQL, use INNER JOIN or IN subqueries.
• Verify expected common rows, as INTERSECT removes duplicates.

EXCEPT/MINUS:

• Use EXCEPT (or MINUS) for unique rows; for MySQL, use LEFT JOIN with IS NULL or NOT IN.
• Test query order to ensure correct subtraction (first query minus second).

General:

• Use ORDER BY at the end of the final set operation to sort the combined result.
• Comment queries for clarity (e.g., -- Combine employee and contractor names).
• Test each query independently before combining with set operations.
• Use EXPLAIN or EXPLAIN ANALYZE to optimize performance.
• Consider joins or subqueries for complex logic to avoid set operation limitations.
• Handle NULLs consistently (e.g., COALESCE for comparisons).