--- title: 'Set Operations' source: 'https://academia.sh/en/courses/sql-fundamentals/set-operations' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Set Operations Combining two result sets as a union, intersection, and difference; whether duplicate rows are kept, the equality of null values in set operations, and how operator precedence depends on the engine. 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. ```sh 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','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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**. ```sh 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. ```sh 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. ```sh 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**. ```sh 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. ```sh 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.