Learn UNION, UNION ALL, INTERSECT, EXCEPT / MINUS with - Textnotes

Learn UNION, UNION ALL, INTERSECT, EXCEPT / MINUS with

Master SQL set operations in this beginner-friendly guide. Learn how to combine query results using UNION, UNION ALL, INTERSECT, and EXCEPT/MINUS. Practice merging student tables and finding common employees in multiple departments.

1. Introduction

Set operations in SQL allow you to combine results from multiple queries into a single result set.

  1. Useful for merging datasets or finding common/different records.
  2. Each query must return the same number of columns with compatible data types.

2. UNION and UNION ALL

2.1 UNION

  1. Combines results of two queries removing duplicates.

Syntax:


SELECT column1, column2 FROM TableA
UNION
SELECT column1, column2 FROM TableB;

Example:


-- Merge students from two tables, removing duplicates
SELECT Name, Class FROM Students_2024
UNION
SELECT Name, Class FROM Students_2025;

2.2 UNION ALL

  1. Combines results including duplicates.

Example:


-- Merge students from two tables including duplicates
SELECT Name, Class FROM Students_2024
UNION ALL
SELECT Name, Class FROM Students_2025;

3. INTERSECT

  1. Returns only rows common to both queries.

Syntax:


SELECT column1, column2 FROM TableA
INTERSECT
SELECT column1, column2 FROM TableB;

Example:


-- Find employees in both Department A and Department B
SELECT Name FROM Employees_DepartmentA
INTERSECT
SELECT Name FROM Employees_DepartmentB;

4. EXCEPT / MINUS

  1. Returns rows from the first query not in the second.
  2. SQL Server uses EXCEPT, Oracle/PostgreSQL uses MINUS.

Syntax (SQL Server):


SELECT column1, column2 FROM TableA
EXCEPT
SELECT column1, column2 FROM TableB;

Example:


-- Employees in Department A but not in Department B
SELECT Name FROM Employees_DepartmentA
EXCEPT
SELECT Name FROM Employees_DepartmentB;

Example (Oracle/PostgreSQL):


SELECT Name FROM Employees_DepartmentA
MINUS
SELECT Name FROM Employees_DepartmentB;

5. Practical Exercises

  1. Merge two student tables into one list using UNION.
  2. Merge two student tables including duplicates using UNION ALL.
  3. Find employees present in both Department A and Department B using INTERSECT.
  4. List employees in Department A but not in Department B using EXCEPT / MINUS.
  5. Compare product lists from two regions to find common and unique products.

6. Tips for Beginners

  1. Ensure column count and data types match in both queries.
  2. Use UNION to remove duplicates, UNION ALL to keep duplicates.
  3. Use INTERSECT for common data, EXCEPT/MINUS for differences.
  4. Set operations are performed after WHERE and GROUP BY clauses in individual queries.


Next Step: After mastering set operations, the next module is Indexing and Performance, where you’ll learn about indexes, query optimization, and execution plans to improve database efficiency.