Set Operations

The previous lesson built grouping: the rows a single query produced were split into sets and each set was summarized. The join and the grouping both worked, throughout, inside a single query.

Some questions instead ask for the results of two separate queries to be compared against each other. “What is common to these two lists,” “what is in the first but not the second,” “what is the sum of the two lists” — all three are operations from set theory, and SQL has direct counterparts for them. This lesson builds those operations.

The distinction is worth stating up front: a join adds rows side by side, increasing the column count. Set operations add rows one below the other, leaving the column count unchanged.

The block below builds the schema and the sample data; every query in this lesson runs against the library.db file it creates.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

Two Result Sets

Two queries will be used throughout the examples. The first gives the authors of books published before 1980, the second gives the authors of books at the central branch.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980 ORDER BY author;
SELECT author FROM book WHERE branch_id = 1 ORDER BY author;
SQL

author           
-----------------
Jorge Luis Borges
Oğuz Atay        
Oğuz Atay        
Yakup Kadri      
author       
-------------
José Saramago
Oğuz Atay    

The first set has four rows and holds a duplicate — the same author appears twice, once for each of two books published before 1980. The second set has two rows. The value the two sets share is Oğuz Atay.

For two queries to be combined with a set operation they must be union compatible: equal column counts, and corresponding columns of comparable types. If the counts are not equal, the query does not run.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title FROM book
UNION
SELECT name, city FROM branch;
SQL

Parse error near line 3: SELECTs to the left and right of UNION do not have the same number of result columns

The column names in the result are taken from the first query; the aliases of later queries are ignored.

Union and Duplicate Rows

A union places the rows of two sets one below the other. It has two spellings, and the only difference between them is what happens to duplicate rows.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980
UNION
SELECT author FROM book WHERE branch_id = 1
ORDER BY author;
SQL

author           
-----------------
Jorge Luis Borges
José Saramago    
Oğuz Atay        
Yakup Kadri      

Four plus two, six rows were expected; four came back. UNION eliminates duplicate rows — both the row common to the two sets and the duplicate inside a single set. Oğuz Atay appeared three times across the inputs and appeared once.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980
UNION ALL
SELECT author FROM book WHERE branch_id = 1
ORDER BY author;
SQL

author           
-----------------
Jorge Luis Borges
José Saramago    
Oğuz Atay        
Oğuz Atay        
Oğuz Atay        
Yakup Kadri      

UNION ALL eliminated nothing: six rows. The distinction is not only a difference in result but a difference in cost. Eliminating duplicates requires the engine to compare every row against every other — it builds a sort or a hash table. UNION ALL does none of that work.

The choice should be deliberate: UNION when duplicates genuinely need to be eliminated, UNION ALL when they do not. Combining two sets known to be disjoint with UNION pays an elimination cost that serves no purpose. In the other direction, writing UNION ALL when duplicates need to be eliminated silently produces inflated counts.

Intersection and Difference

Intersection returns the rows found in both sets.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980
INTERSECT
SELECT author FROM book WHERE branch_id = 1;
SQL

author   
---------
Oğuz Atay

Difference returns the rows found in the first set but not in the second.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980
EXCEPT
SELECT author FROM book WHERE branch_id = 1;
SQL

author           
-----------------
Jorge Luis Borges
Yakup Kadri      

Difference is directional: reversing the order changes the result. Intersection and union are not.

Both operations eliminate duplicate rows by default. Forms that keep duplicates are defined in the standard, but their support varies by engine.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE publication_year < 1980
INTERSECT ALL
SELECT author FROM book WHERE branch_id = 1;
SQL

Parse error near line 3: near "ALL": syntax error
  FROM book WHERE publication_year < 1980 INTERSECT ALL SELECT author FROM book 
                                      error here ---^

The name of the difference operation also varies by engine: some engines use a different keyword instead of EXCEPT. When portability matters, set operations should be tested against the target engine, name and options together.

Null Values Are Equal in Set Operations

The trap measured in the fifth lesson is worth recalling: a single null value inside a NOT IN list dropped a query’s row count to zero. Set operations do not carry the same trap, because they treat null values as equal to each other, the same way grouping does.

sqlite3 library.db <<'SQL'
.headers on
.mode box
SELECT NULL AS union_result UNION SELECT NULL;
SELECT NULL AS union_all_result UNION ALL SELECT NULL;
SQL

┌──────────────┐
│ union_result │
├──────────────┤
│              │
└──────────────┘
┌──────────────────┐
│ union_all_result │
├──────────────────┤
│                  │
│                  │
└──────────────────┘

The framed output format makes the row count visible here: two null values collapsed to one row under a union, while the UNION ALL spelling kept two rows. A null value is not equal to itself with respect to the comparison operator, but it is equal to itself with respect to set operations.

This has a direct consequence: when the question “which branch has no books at all” is written as a difference operation, the null branch id in the book table does not corrupt the result.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT branch_id FROM branch
EXCEPT
SELECT branch_id FROM book;
SQL

branch_id
---------
4        

The correct answer is a single row. Had the same question been written with NOT IN, the null id in the book table would have caused it to return no row at all. A difference operation is a safer choice than NOT IN when a column may hold null values.

Precedence and Ordering

When more than one set operator appears in a single statement, evaluation order starts to matter. The standard gives intersection higher precedence than union and difference. Its application varies by engine.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE branch_id = 1
UNION
SELECT author FROM book WHERE publication_year < 1980
INTERSECT
SELECT author FROM book WHERE publication_year > 1980;
SQL

author       
-------------
José Saramago

This engine applied the operators left to right: union first, then intersection. Had the intersection applied first, the second and third sets would have shared no row, the intersection would have come back empty, the union would have returned the first set unchanged, and the result would have been two rows. Grouping with parentheses is not accepted everywhere either.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT author FROM book WHERE branch_id = 1
UNION
(SELECT author FROM book WHERE publication_year < 1980
 INTERSECT
 SELECT author FROM book WHERE publication_year > 1980);
SQL

Parse error near line 3: near "(": syntax error
  SELECT author FROM book WHERE branch_id = 1 UNION (SELECT author FROM book WHE
                                      error here ---^

Mixing different set operators inside a single statement is, as a result, not a portable spelling. Where a mixed combination is required, structures that name an intermediate result — the common table expressions covered in the Advanced SQL course — both make the order explicit and keep the query readable.

There is a rule for ordering too: the ORDER BY clause applies not to the individual queries but to the whole combined result, and it is written once, at the end of the statement. The sort key is therefore referred to by the column names the first query produces.

Summary

• A join adds rows side by side, set operations add rows one below the other; set operations do not change the column count.
• Queries entering a set operation must be union compatible; column names are taken from the first query.
• UNION eliminates duplicate rows and pays a comparison cost for it; UNION ALL eliminates nothing.
• Intersection and difference eliminate duplicates by default; support for the forms that keep duplicates and for the name of the difference operator varies by engine.
• Set operations treat null values as equal to each other, which makes a difference operation a safer choice than NOT IN.
• The precedence of mixed set operators in a single statement varies by engine; ORDER BY applies to the whole combined result.

Next Step

Everything done throughout this topic was reading: existing rows were selected, filtered, joined, summarized, grouped. The data was there the whole time — written once, in the first lesson, and never changed again. The turn is now to writing the data and the schema that holds it. The next topic’s first lesson takes up creating tables: column definitions, type choices, and how constraints are expressed in a schema. The integrity rules designed on paper in the Data Modeling and Relational Theory course become, there, a definition that actually runs.