Everything you've done in this module consists of matching rows horizontally: taking a row from orders, finding its partner in customers and gluing them side by side to get a wider row. JOINs add columns.

There's a second, completely different way of combining: stacking results vertically. Two independent queries are run, each with its own columns, and their rows are put one below the other. The set operators —UNION, INTERSECT and EXCEPT— add rows.

This lesson closes module 3. With it you'll have both ways of combining information in SQL and you'll know which to reach for in each case.

1. JOIN versus set operators
2. The compatibility rules
3. UNION and UNION ALL
4. The unified contact list
5. INTERSECT: what's in both
6. EXCEPT: what's in the first and not in the second
7. EXCEPT versus 03-03's anti-join
8. INTERSECT ALL and EXCEPT ALL
9. Precedence and parentheses
10. ORDER BY and LIMIT over the combined result
11. Support by engine
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

The key difference is the second half of the third row: set operators don't need the tables to be related. You can combine the results of two queries over tables that don't share a single foreign key, as long as their columns line up.

For two queries to be combinable there are three rules, and all three are strict:

1. The same number of columns.

2. Compatible types, column by column, in the same order.

Column 1 of the first query is combined with column 1 of the second, 2 with 2, and so on. The names don't matter; the position does. PostgreSQL applies its implicit conversion rules: INTEGER and NUMERIC combine without any trouble, VARCHAR and TEXT too, but DATE and VARCHAR don't.

3. The result's column names come from the first query.

The result's column is called contact. The second query's alias is ignored entirely, and that's a classic source of confusion when reading other people's code.

Practical consequence: write the aliases in the first query and don't bother repeating them in the others (although doing so helps document what each column is).

What happens if one table doesn't have a column the other one has? In GreenStore, customers and suppliers have email, but employees doesn't. The solution is to fill the gap with a literal:

The ::varchar is an explicit cast. Without it, PostgreSQL can sometimes infer the NULL's type from the other branch, but not always; writing it avoids the failed to determine data type of column error. Casts are studied in depth in lesson 06-04.

UNION stacks the rows of two queries and removes duplicates. UNION ALL stacks them and removes nothing.

Let's see it with the cities where GreenStore has a presence:

11 rows. And the same query with UNION ALL returns 23 rows: the 15 cities from customers (with Valencia repeated four times and Barcelona twice) plus the 8 from employees (with Valencia seven times).

UNION without ALL does extra work that is often not merely unnecessary but wrong:

• It's slower. Deduplicating requires sorting the whole result or maintaining a hash structure in memory. With millions of rows, the difference is enormous.
• It can delete legitimate rows. If two different customers had the same name and lived in the same city, SELECT name, city FROM customers UNION ... would collapse them into one. You'd have lost a customer without noticing.
• It compares the whole row. Two rows are duplicates only if all their columns match. Adding an id column to the SELECT makes UNION stop removing anything, and then you're only paying the cost.

Course rule: use UNION ALL by default. Fall back on UNION only when removing duplicates is the query's explicit goal, as in the city listing above. It's the same philosophy as DISTINCT in 02-04: if you need it to "fix" a result, check first whether the query is properly framed.

This is also why MySQL's FULL OUTER JOIN emulation (03-05) uses UNION and not UNION ALL: there the two halves do produce the same matching rows, and they have to be removed.

A real case: the company wants a single address book with everyone and everything it deals with, flagging where each entry comes from. The three sources live in different tables with no relationship between them: it's the perfect scenario for UNION ALL.

28 rows = 15 customers + 8 employees + 5 suppliers.

Four design decisions in this query deserve comment:

Notice the alphabetical order as well: "Óscar" appears between "Marc" and "Rosa" because the database uses the en-US-x-icu collation you configured in 02-05. With the default C collation, "Óscar" would go at the end of the block, after "Rosa".

INTERSECT returns the rows that appear in both queries.

In which cities do we have both customers and employees?

Two cities. Valencia, where the head office is and where four customers live, and Castellón, where Laia Puig Sanchis works and Marta Sanchis Gil lives. It's a query with an immediate business reading: these are the cities where you could organise a hand delivery or an event with customers.

In which countries do we have both customers and suppliers?

Three countries. Germany is left out because there's a supplier there (EcoNordic Supplies) but no customer.

Two properties of INTERSECT worth knowing:

• It removes duplicates by default, just like UNION. If a city appeared five times in customers and three in employees, the result shows it once.
• It's commutative: A INTERSECT B and B INTERSECT A give the same thing. It's the property EXCEPT lacks.

EXCEPT returns the rows of the first query that don't appear in the second. It's the set difference.

Which products have never been sold? The catalogue's identifiers, minus the identifiers that appear in the sales:

The same three as ever. The query reads almost like the question: "all the products, minus the ones that have been sold".

In which countries do we have a supplier but no customer?

One row. And now the key thing about EXCEPT: it isn't commutative. Turn it around:

Zero rows: there's no country with customers where we don't also have a supplier. The two queries answer different questions, and confusing them is the most frequent mistake with this operator.

Dialect note: in Oracle this operator is called MINUS, not EXCEPT. Since Oracle 21c EXCEPT is also accepted as a synonym, but you'll find MINUS in all the older code. The behaviour is identical.

You now have two ways of answering "which products have never been sold?". Compare them:

Both identify products 13, 19 and 20. But they aren't interchangeable:

When to choose each: if you need data from the resulting rows, use the anti-join. If you only need the list of identifiers —for a count, an integrity check, a reconciliation report— EXCEPT is shorter and reads better.

In module 7 a third form will appear, NOT EXISTS with a correlated subquery, which combines the best of both: it returns the whole row and reads like the question. And a fourth, NOT IN, which looks the most natural and has treacherous behaviour around nulls. All four are compared in lesson 07-05.

Just as UNION has its ALL variant, INTERSECT and EXCEPT have one too. Their semantics are multiset: instead of working with presence or absence, they count how many times each row appears.

An example over the cities: Valencia appears 4 times in customers and 7 times in employees.

It returns Valencia 4 times —min(4, 7)— and Castellón 1 time —min(1, 1)—: 5 rows in total. With plain INTERSECT there'd be 2.

It returns Valencia 3 times —max(7 − 4, 0)— and nothing else: max(1 − 1, 0) = 0 for Castellón.

In practice they're used very little. Their ground is data-quality checks of the "has this migration duplicated rows?" kind, where the number of repetitions matters and not just their presence. It's useful to know they exist; it isn't common to write them.

Dialect note: INTERSECT ALL and EXCEPT ALL are standard and present in PostgreSQL, but not in SQL Server or Oracle (where MINUS has no ALL variant).

When three or more queries are chained, the order of evaluation matters. The SQL standard establishes that:

INTERSECT has higher precedence than UNION and EXCEPT, which are evaluated left to right relative to each other.

That is, A UNION B INTERSECT C means A UNION (B INTERSECT C), just as 2 + 3 * 4 means 2 + (3 * 4).

Let's see it with a case that changes radically depending on the grouping. The query:

PostgreSQL evaluates it as products EXCEPT (order_lines INTERSECT reviews):

1. order_lines INTERSECT reviews = the products that have been sold and have a review = 9 products (1, 2, 5, 6, 10, 12, 15, 16, 18).
2. products EXCEPT those 9 = 11 products: 3, 4, 7, 8, 9, 11, 13, 14, 17, 19 and 20.

They're exactly the 11 products with no review at all. Now let's force the other grouping with parentheses:

Zero rows, because (products EXCEPT order_lines) is the three never-sold products (13, 19, 20) and none of them has a review. The same query, two groupings, 11 rows against 0.

Course rule: as soon as there are three or more queries chained with different operators, always use parentheses, even when they match the default precedence. They cost two characters and remove all ambiguity for whoever reads the code.

Important dialect note: SQLite doesn't implement this precedence. It evaluates compound operators strictly left to right, so this section's first query would return 0 rows in SQLite and 11 in PostgreSQL, MySQL, SQL Server and Oracle. It's a decisive argument in favour of explicit parentheses: with them, the query means the same thing on every engine.

ORDER BY and LIMIT don't belong to either query: they're applied to the combined result and they go at the very end.

The first five cities in alphabetical order from the already unified set. Important details:

If you need to sort or limit one of the queries separately, it has to be wrapped in parentheses:

It's valid syntax in PostgreSQL, though uncommon: what you want is almost always to sort the total.

In the logical execution order, the step looks like this: each query is resolved completely (with its FROM, its JOINs, its WHERE and its SELECT), then the set operator is applied, and only then is it sorted and trimmed.

UNION is the only one of the three you can take for granted on any engine and any version. If you write portable SQL and need INTERSECT or EXCEPT on old MySQL, the alternative is a JOIN (for the intersection) or an anti-join (for the difference), which is exactly what everybody did before 2022.

• A different number of columns. ERROR: each UNION query must have the same number of columns. Count the columns in each branch before running it.
• Incompatible types in the same position. Columns are matched by position, not by name. A DATE in position 2 of one branch and a VARCHAR in position 2 of the other gives an error.
• Expecting the second query's alias to be used. The result's names always come from the first one.
• Using UNION when you meant UNION ALL. It removes legitimately repeated rows and costs more. By default, UNION ALL.
• Using UNION ALL when there really was overlap. In the FULL OUTER JOIN emulation (03-05) it would duplicate every matching row.
• Reversing the order of an EXCEPT. It isn't commutative: suppliers EXCEPT customers gives Germany; the other way round, zero rows.
• Putting an ORDER BY in an intermediate query. ERROR: syntax error at or near "UNION". It goes at the end, once, and affects the total.
• Chaining three operators with no parentheses. INTERSECT is evaluated before UNION and EXCEPT in the standard, but not in SQLite. The same query returned 11 rows and 0 rows depending on the grouping.
• Forgetting the source column in a UNION of different sources. Without it you can't tell whether "Laia Puig Sanchis" is an employee or a customer.
• Tip: write the first branch, run it, and only then add the rest. Debugging a UNION of four queries written in one go is uncomfortable; type errors point at the union, not at the guilty column.
• Tip: line the branches up visually. The same column order, the same indentation and the operator on a line of its own. A well-formatted UNION can be reviewed at a glance.
• Tip: use ORDER BY 1, 2 in set queries. Names can be misleading when the branches come from different tables; ordinals can't.

Build a directory of Spanish contacts: every person and entity in GreenStore whose country is Spain, with a column stating their type (customer, employee or supplier), their name and their city if there is one.

Bear in mind that employees has no country column —they all work in Spain— and that suppliers has no city. Sort by type and name.

Answer with set operators:

1. In which cities are there customers but no employees?
2. In which cities are there employees but no customers?
3. Explain why the two results are so different in size.

A colleague wants the list of products that have been sold but have no review at all, and writes this:

1. How does PostgreSQL group this query and what does it actually return?
2. Write the correct query for what he wanted.
3. Write the same answer using a JOIN and an anti-join instead of set operators, returning the product's name too. Which of the two do you prefer, and why?

Solution 1

21 rows = 11 Spanish customers + 8 employees + 2 Spanish suppliers.

Three details of the reasoning:

• employees carries no WHERE because the table has no country column: by design, the whole team works in Spain. It's an assumption of the model, and it's worth leaving it written in a comment so nobody takes it as an oversight.
• The filters go in each branch's WHERE, not at the end. A WHERE after the last UNION ALL would belong only to the third query, not to the whole set.
• NULL::varchar keeps the number of columns right in the suppliers branch.

Solution 2

1. Cities with customers but no employees:

9 cities.

2. Cities with employees but no customers:

None.

3. Why they're so different. Because the two sets have very different sizes and natures:

The employees' cities are a subset of the customers': GreenStore only has offices in Valencia (head office) and Castellón (where Laia works), and there are customers in both. That's why employees EXCEPT customers is empty, while the reverse operation returns the other nine cities where there are customers and no physical presence.

A cross-check against section 5: 11 cities in total (UNION), 2 in common (INTERSECT), 9 customers-only (EXCEPT) and 0 employees-only. The numbers add up: 2 + 9 + 0 = 11.

Solution 3

1. How PostgreSQL groups it. There's no INTERSECT in the query, so the two remaining operators are evaluated left to right:

The first part gives the 8 sold products with no review; the second adds the catalogue's 20 products. The union of both sets is... the 20 products. The query returns the whole catalogue, which has absolutely nothing to do with the question. The UNION with products cancels all the EXCEPT's work.

2. The correct query. The third branch is entirely superfluous:

8 products sold at some point and never reviewed. Compare it with the 11 products with no review from section 9: the difference is the three that have never been sold (13, 19 and 20), which don't appear here because they aren't in order_lines.

3. With a JOIN and an anti-join:

The same 8 products, now with their name and price. The INNER JOIN with order_lines demands that they've been sold; the LEFT JOIN with reviews plus the WHERE r.id IS NULL demands that they have no review.

Which to prefer:

For this particular case, the JOIN is the better option, because a report with eight nameless numbers is no use to anybody. EXCEPT would be preferable if the query were an intermediate step of an automated check, where only the identifiers matter.

And notice the DISTINCT: it's exactly the symptom we talked about in 02-04 and 03-02. It shows up because the INNER JOIN with order_lines generates one row per sale, and the question is about products, not about sales. In module 7 you'll see that WHERE EXISTS (...) solves this without a DISTINCT and without multiplying rows.

You've closed the module's last piece:

• Set operators combine vertically: they stack rows from independent queries, whereas JOINs combine horizontally by adding columns. They need no relationship between the tables.
• The compatibility rules are three: the same number of columns, compatible types by position and result names taken from the first query. Gaps are filled with literals such as NULL::varchar.
• UNION ALL is the default choice: it doesn't deduplicate, it's faster and it doesn't delete legitimately repeated rows. UNION only when removing duplicates is the goal, as in the list of 11 cities against UNION ALL's 23.
• You've built the unified contact list (28 rows from three unrelated tables) with a literal source column, which is what makes any UNION of heterogeneous sources readable.
• INTERSECT returns what's on both sides and is commutative: two cities with customers and employees, three countries with customers and suppliers.
• EXCEPT returns what's in the first and not in the second and isn't commutative: Germany one way round, zero rows the other. In Oracle it's called MINUS.
• You know when to choose EXCEPT and when 03-03's anti-join: EXCEPT for lists of identifiers, the anti-join when you need the rows' data.
• INTERSECT takes precedence over UNION and EXCEPT —except in SQLite, which evaluates left to right— and the same query can return 11 rows or 0 depending on the grouping. Always use parentheses when you chain three or more.
• ORDER BY and LIMIT go at the end and apply to the combined result, using the first query's column names or the ordinals.

GreenStore's nine tables have stopped being nine islands. You know:

• That a JOIN is a filtered cartesian product, that its condition goes in the ON and that JOINs are resolved inside the FROM, before the WHERE.
• How to use the INNER JOIN and predict which rows it loses, with the canonical four-table query —order_lines + orders + customers + products— that will stay with you all the way to the final project.
• How to use the LEFT JOIN to keep what doesn't match, the anti-join pattern to find it, and why a condition in the WHERE over the right table silently degrades the LEFT into an INNER.
• That the RIGHT JOIN is its mirror and can always be rewritten as a LEFT, and that mixing them in a chain makes the query unreadable.
• That the FULL OUTER JOIN is for reconciling two independent sources, and that referential integrity makes it unnecessary within a single schema.
• How to walk reflexive relationships with a SELF JOIN —the org chart and the referral network— and generate complete combinations with a CROSS JOIN.
• And how to stack whole results with UNION, INTERSECT and EXCEPT.

But notice what every query you've written in this module returns: detail rows. One row per order line, one per customer, one per pair of products. And the questions GreenStore's management really asks don't want detail, they want totals: how much each category bills, how many orders each customer has placed, what each product's average rating is, which sales rep closes the most sales, how many customers there are per country. To answer them you have to summarise many rows into one, and that's an operation you still don't know how to do.

In module 4, Advanced Filtering and Aggregation, the two missing halves arrive. First you'll sharpen the filtering: LIKE for searching by text patterns, IN and BETWEEN for ranges and lists, and the serious treatment of NULLs with IS NULL, IS NOT NULL and their three-valued logic —the one you've already seen peeking out in the ON, in the WHERE and in every LEFT JOIN of this module. And then comes aggregation: the COUNT, SUM, AVG, MIN and MAX functions, the GROUP BY clause that splits the result into groups and HAVING to filter those groups. There, this time with real consequences, the warning you've read three times in this module will come back: be careful about summing header values after joining with the detail.