SQL - Set Operators
Overview
Set operators combine results. UNION removes duplicates, UNION ALL keeps them. INTERSECT returns common rows. EXCEPT (MINUS) returns rows in the first query not in the second.
SELECT city FROM employees
UNION
SELECT city FROM customers;
SELECT city FROM employees
INTERSECT
SELECT city FROM customers;
SELECT city FROM employees
EXCEPT
SELECT city FROM customers; -- MySQL uses EXCEPT in 8.0.31+, earlier use LEFT JOIN filtersSELECT city FROM employees
UNION
SELECT city FROM customers;
SELECT city FROM employees
INTERSECT
SELECT city FROM customers;
SELECT city FROM employees
EXCEPT
SELECT city FROM customers;
SELECT city FROM employees
UNION
SELECT city FROM customers;
SELECT city FROM employees
INTERSECT
SELECT city FROM customers;
SELECT city FROM employees
EXCEPT
SELECT city FROM customers;
Prerequisite
Load the Standard Test Data.
Expected Output (live)
UNION cities
| city |
|---|
| Austin |
| Boston |
| New York |
| San Francisco |
| Seattle |
INTERSECT cities
| city |
|---|
| Austin |
| Boston |
| New York |
| San Francisco |
| Seattle |
EXCEPT cities
No rows