Set Operations

Set operations combine the results of two or more SELECT queries. Stoolap supports UNION, UNION ALL, INTERSECT, and EXCEPT.

UNION

Combines results from two queries, removing duplicate rows:

SELECT name FROM employees
UNION
SELECT name FROM contractors;

UNION ALL

Keeps all rows including duplicates (faster since no deduplication is needed):

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

INTERSECT

Returns only rows that appear in both result sets:

SELECT name FROM current_employees
INTERSECT
SELECT name FROM bonus_recipients;
-- Returns employees who received a bonus

INTERSECT ALL

Keeps duplicate rows in the intersection (a duplicate appears as many times as it exists in both inputs):

SELECT name FROM current_employees
INTERSECT ALL
SELECT name FROM bonus_recipients;

EXCEPT

Returns rows from the first query that do not appear in the second:

SELECT name FROM all_employees
EXCEPT
SELECT name FROM terminated_employees;
-- Returns only active employees

Order matters: A EXCEPT B is different from B EXCEPT A.

EXCEPT ALL

Keeps duplicates: each matching row from the second query removes one occurrence from the first:

SELECT name FROM all_employees
EXCEPT ALL
SELECT name FROM terminated_employees;

Chaining Multiple Operations

Set operations can be chained:

SELECT id FROM table1
UNION
SELECT id FROM table2
UNION
SELECT id FROM table3
UNION
SELECT id FROM table4;

ORDER BY and LIMIT

ORDER BY and LIMIT apply to the entire combined result set:

SELECT name, salary FROM employees
UNION
SELECT name, salary FROM contractors
ORDER BY salary DESC
LIMIT 10;

With WHERE Clauses

Each SELECT in a set operation can have its own WHERE clause:

SELECT val FROM sales WHERE val > 1000
UNION
SELECT val FROM returns WHERE val < 500;

Column Requirements

All queries in a set operation must have the same number of columns. Column names are taken from the first query:

-- Both queries must return 2 columns
SELECT id, name FROM employees
UNION
SELECT id, name FROM contractors;

Duplicate Handling Summary

Operation	Duplicates
UNION	Removed
UNION ALL	Kept
INTERSECT	Removed
INTERSECT ALL	Kept (min count from both sides)
EXCEPT	Removed
EXCEPT ALL	Kept (subtracts one per match)