UNION, INTERSECT, EXCEPT

SQL supports combining results from two or more queries (with the same number of columns and compatible data types) using set operators. The main ones are:

1. UNION

• Combines the results of two queries.
• Removes duplicates by default.
• Columns in both queries must have the same number and compatible data types.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

➡️ Returns all distinct cities from both customers and suppliers.

2. UNION ALL

• Same as UNION, but keeps duplicates.

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

➡️ Returns all cities from both, including duplicates.

3. INTERSECT

• Returns only the rows present in both queries.
• Removes duplicates automatically.

SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

➡️ Returns cities that exist in both customers and suppliers.

4. EXCEPT (in SQL Server, PostgreSQL) / MINUS (in Oracle)

• Returns rows from the first query that are not in the second.
• Removes duplicates automatically.

SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

➡️ Returns cities in customers but not in suppliers.

⚡ Key Notes

• The number of columns and their data types must match.
• Ordering is not guaranteed; use ORDER BY at the end of the whole set operation.
• Availability differs:
  • UNION / UNION ALL → supported in all major databases.
  • INTERSECT → not in MySQL (workaround: use INNER JOIN).
  • EXCEPT → SQL Server/Postgres; in Oracle use MINUS.