Set Operations — UNION / INTERSECT / EXCEPT

Data used in this article — employee and contractor

Set operations (operations that combine multiple SELECT results into one as the union, intersection, or difference of sets) are the syntax for stacking separately fetched results vertically, or pulling out the rows common to both or the rows that appear in only one side.

You'll work with four of them: UNION (union), UNION ALL (union that keeps duplicates), INTERSECT (intersection), and EXCEPT (difference).

The material is the employee table employee (30 rows) and the contractor table contractor (6 rows).

You'll try the four set operations one by one — joining two results with different conditions inside employee, or combining results pulled from different tables like employee and contractor into one.

Before diving into the exercises, take a look at the column definitions and a sample of the data for the two tables this article uses — employee and contractor.

① Run PRAGMA table_info(employee); and PRAGMA table_info(contractor); to check the column definitions of both tables.

② Run SELECT * FROM employee LIMIT 5; and SELECT * FROM contractor LIMIT 5; to preview the first 5 rows of data.

The premise of set operations — being union-compatible

All four set operations join two or more SELECT results in the form SELECT ... operator SELECT ....

The premise for joining is that the upper and lower SELECT are union-compatible (equal number of columns, and the corresponding column types are compatible).

SELECT name FROM employee and SELECT name FROM contractor are both a single string column, so they're compatible.

On the other hand, SELECT name FROM employee and SELECT name, city FROM contractor have a different number of columns, so you can't join them.

-- union-compatible: both sides are 2 columns (name, city)
SELECT name, city FROM employee WHERE city = 'Tokyo'
UNION
SELECT name, city FROM contractor
ORDER BY name;

-- ORDER BY goes just once at the very end
-- you can't write a per-SELECT ORDER BY in the middle

UNION and UNION ALL — remove duplicates or keep them

UNION stacks two results vertically and merges completely identical rows into one (duplicate removal).

UNION ALL does no duplicate removal and keeps every row from both sides as-is.

When you don't need to remove duplicates, or when you want to keep "how many rows appeared in both," use UNION ALL.

UNION runs an internal sort to detect duplicates, so UNION ALL is the lighter operation.

The example below joins the "living in Kyoto" result and the "salary 7 million or more" result inside employee.

An employee who satisfies both conditions becomes 1 row with UNION, and 2 rows with UNION ALL.

-- UNION: duplicate rows are merged into one
SELECT name FROM employee WHERE city = 'Osaka'
UNION
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

-- UNION ALL: duplicates are kept (rows matching both appear twice)
SELECT name FROM employee WHERE city = 'Osaka'
UNION ALL
SELECT name FROM employee WHERE salary >= 5000000
ORDER BY name;

Imagine the requirement: "I want to combine employees living in Kyoto and employees with a high salary into one roster. Anyone who fits both should appear only once." (Run it correctly and the explanation appears.)

① Write a SELECT that pulls the name from employee where city is Kyoto.

② Join it with a SELECT that pulls the name of rows where salary is 7,000,000 or more, using a set operator that removes duplicates.

③ Order the result by name ascending (to guarantee the row order).

Imagine the requirement: "I want to stack the results of the same two conditions as Practice 1 without removing duplicates — all of them — so I can visually confirm which names appeared in both."

① Join the same two SELECTs as Practice 1 (the name for living in Kyoto / salary 7,000,000 or more) using a set operator that keeps duplicates.

② Order the result by name ascending. Confirm that a name appearing in both shows up as two consecutive rows.

INTERSECT and EXCEPT — common rows and difference rows

• INTERSECT (intersection) returns only the rows that appear in common in both results.
• EXCEPT (difference) returns the rows that are in the upper result but not in the lower result.

Both automatically remove duplicates (the same behavior as UNION).

EXCEPT is an asymmetric operation whose result changes if you swap the upper and lower sides — A EXCEPT B and B EXCEPT A are different things.

The example below joins the name for "living in Kyoto" and "salary 6.5 million or more" inside employee with INTERSECT, pulling out the employees who satisfy both (the common rows).

-- INTERSECT: employees living in Osaka AND with salary 6000000 or more
SELECT name FROM employee WHERE city = 'Osaka'
INTERSECT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

-- EXCEPT: employees living in Osaka but NOT with salary 6000000 or more
SELECT name FROM employee WHERE city = 'Osaka'
EXCEPT
SELECT name FROM employee WHERE salary >= 6000000
ORDER BY name;

Solve the requirement "I want to pull out only employees living in Kyoto AND with a salary of 6,500,000 or more" using set operations.

① Write a SELECT that pulls the name from employee where city is Kyoto.

② Join it with a SELECT that pulls the name of rows where salary is 6,500,000 or more, using a set operator that returns only the common rows.

③ Order the result by name ascending.

Imagine the requirement: "Of the employees living in Kyoto, I want to pull out only those who are NOT at salary 6,500,000 or more (the lower-paid Kyoto folks)."

① Write a SELECT that pulls the name from employee where city is Kyoto.

② Join it with a SELECT that pulls the name of rows where salary is 6,500,000 or more, using a set operator that returns rows in the upper side but not in the lower side (be careful — the order of the upper and lower sides affects the result).

③ Order the result by name ascending.

Set operations across different tables — employee and contractor

Set operations work not just within a single table but also between results pulled from separate tables.

As long as they're union-compatible (matching column count and type), the tables can differ.

When you want to sort the joined result, write a single ORDER BY after the very last SELECT (it applies to the entire joined result).

Joining the name of the employee table employee and the contractor table contractor with INTERSECT lets you surface people who appear in both with the same name.

The example below pulls only the single name column and joins them with INTERSECT.

Since both tables' name have Alice and Bob, 2 people are returned as common rows.

-- names that appear in both employee and contractor
SELECT name FROM employee
INTERSECT
SELECT name FROM contractor
ORDER BY name;

-- names in contractor but not in employee (external only)
SELECT name FROM contractor
EXCEPT
SELECT name FROM employee
ORDER BY name;

Imagine the requirement: "I want to list the people in the contractor table contractor whose same name does NOT exist in the employee table employee (purely external-only names)."

① Write a SELECT that pulls the name from contractor.

② Join it with a SELECT that pulls the name from employee, using a set operator that returns rows in the upper side but not in the lower side. Place the contractor SELECT on top.

③ Order the result by name ascending.