MySQL - UNION, INTERSECTION, and DIFFERENCE

In the context of databases and SQL, operations like UNION, INTERSECTION, and DIFFERENCE (also called EXCEPT) are used to combine or compare results from two or more queries. 

1. UNION

  • Purpose: Combines the result sets of two or more queries into a single result set.

  • Rules:

    1. Queries must have the same number of columns.

    2. Columns must have compatible data types.

  • Duplicates: UNION removes duplicates; UNION ALL keeps them.

Syntax

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Example

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

  • Returns all unique names from both tables.

2. INTERSECTION

  • Purpose: Returns only the common rows between two queries.

  • MySQL Note: MySQL doesn’t have a direct INTERSECT keyword (unlike SQL Server or Oracle), but you can simulate it with INNER JOIN or IN.

Example using IN

SELECT name FROM customers
WHERE name IN (
    SELECT name FROM suppliers
);

  • Returns names that are present in both customers and suppliers.

3. DIFFERENCE (EXCEPT)

  • Purpose: Returns rows from the first query that do not exist in the second query.

  • MySQL Note: MySQL doesn’t have EXCEPT, but you can use NOT IN or LEFT JOIN ... WHERE NULL.

Example using NOT IN

SELECT name FROM customers
WHERE name NOT IN (
    SELECT name FROM suppliers
);

  • Returns names that are in customers but not in suppliers.

4. Summary Table

Operation Meaning MySQL Syntax / Notes
UNION All unique rows from both queries SELECT ... UNION SELECT ...
UNION ALL All rows including duplicates SELECT ... UNION ALL SELECT ...
INTERSECTION Rows common to both queries Use IN or INNER JOIN
DIFFERENCE Rows in first query but not in second Use NOT IN or LEFT JOIN ... WHERE NULL

5. Practical Example

Tables:

customers

name
Alice
Bob
Carol

suppliers

name
Bob
Dave

  • UNION: Alice, Bob, Carol, Dave

  • INTERSECTION: Bob

  • DIFFERENCE (customers - suppliers): Alice, Carol