Skip to content
Learn/Query and analytics

Query and analytics

Learn each concept by watching it work, then put it into practice.

On this page
Inner joinOuter joinsAnti-joinSemi-joinSelf joinSet operationsCross joinNon-equi join

Inner join

Combine two tables into one, keeping only the rows that match on both sides.

6 min read

Overview

An inner join combines two tables into one, keeping only the rows that find a match on both sides. You reach for it whenever the answer needs columns that live in separate tables. Here are two: customers lists who they are, and orders records what they bought. Each order stores the id of the customer who placed it.

customers
idname
1Ana
2Ben
3Cara
orders
idcustomer_idtotal
101140
102125
103260

Trace each customer_id into customers. Predict the result row count and name the customer who contributes no row.

How it works

Name the two tables, then match a column on one to a column on the other. Each order’s customer_id looks up a customer’s id, and the two rows become one. The aliases o and c let you say which table each column comes from.

select c.name, o.id, o.total
from orders o
join customers c on o.customer_id = c.id
order by o.id;

Run the demo and compare its rows with your prediction.

Follow the keyPress play, or step through it yourself.
orders o
idcustomer_idtotal
101140
102125
103260
customers c
idname
1Ana
2Ben
3Cara
result
nameidtotal
o.customer_id = c.id

The result has three rows. Ana appears twice because two orders match her, while Ben appears once. Cara disappears because no order creates a matching pair for her. That is what inner means here: a row survives only as part of a match.

Patterns

The same move, following a key from one table to another, covers most of what you will write.

  1. 1
    One customer, many orders

    The match is one-to-many here, so Ana appears twice in the result. That repetition is correct, yet it also inflates a careless aggregate. Summing a customer column after this join counts it once per order instead of once per customer. Pitfalls covers the consequences.

  2. 2
    Chain more than two tables

    Joins compose. To bring in a third table, add another join with its own condition. Each join follows one more key.

    select c.name, o.id, r.name as region
from orders o
join customers c on o.customer_id = c.id
join regions r   on c.region_id = r.id;
  3. 3
    Join, then aggregate

    Once the rows are together, group them. To total each customer’s spend, join orders to customers and sum by customer with group by c.name and sum(o.total). The join brings the rows together and the group-by rolls them up.

Trade-offs

An inner join answers “show me the rows that go together.” Sometimes the rows that do not go together are the answer. Think of every customer including the ones who never ordered. An inner join cannot give you Cara because she has no match. You will meet outer joins next. They keep the unmatched rows and fill the gaps with null. Use an inner join for the matches, and a left join when a missing match is itself the point.

Pitfalls

Fan-out double counts

Joining a customer to their orders repeats the customer once per order. Ana’s two orders make two Ana rows. Sum or count a customer column after that and you inflate it, because you are now counting orders rather than customers. When you aggregate across a join, be sure of what one row now means.

  • A missing condition is a cross join. Drop the on clause and every order pairs with every customer. Three and three become nine, and the numbers look plausible.
  • The drop is silent. An inner join omits unmatched rows without any signal. A customer with no order disappears, an order with a bad key disappears, and the result still looks correct.
  • Match on the right key. Join on the column that actually names the relationship, because a join on the wrong column still returns plausible-looking rows and the mistake does not announce itself.
  • Keys can be more than one column. When two columns define the relationship, join on both with on o.customer_id = c.id and o.region = c.region. Matching only half of a composite key is the same wrong-key mistake in a subtler form, and the incomplete match fans out.

Performance

A selective equality key can be matched efficiently, while a missing or partial key can approach n times m comparisons and fan out the result. Check the full relationship and intended result grain before tuning.

Practice

EasyJoin orders to customers through the foreign-key relationship and return one row per matched order with its customer name. The row count should equal matched orders rather than total customers.MediumCompute customer spend totals after joining on the customer key. Reconcile the output to the orders so each contributes once and customers with none are absent.HardBuild an order-to-customer-to-region query one relationship at a time. Each output order must have exactly one region, and the row count must not fan out.

Recap

  • Reach for an inner join when the answer needs columns from matching rows and unmatched rows do not belong.
  • Predict the output grain before running it, because one-to-many matches repeat the one side.
  • Check every column in the relationship key. A partial key can create plausible fan-out.
  • Check aggregates after the join against the intended grain so repeated rows do not inflate them.
  • Move to a left join when the missing match is part of the answer.

Outer joins

Keep every row, even the ones with nothing to match.

6 min read

Overview

An inner join keeps the rows that match, and this guide covers what happens to the rows that do not.

What should happen to a row that has no match?

Take the same customers and orders. Ana and Ben each placed an order and Cara placed none. To list every customer and their orders including Cara, an inner join is no help. It drops her because she has nothing to match.

customers
idname
1Ana
2Ben
3Cara
orders
idcustomer_idtotal
101140
102260

Cara has no order. Before reading on, decide whether a query that must list every customer should keep her and what should fill her order columns.

How it works

The table written first is the left side. The demo runs the same data under left and inner semantics, so follow Cara and watch her order columns.

Press play and verify both parts of your prediction by toggling left and inner.

Keep or drop the unmatchedPress play, or step through it yourself.
select c.name, o.id, o.total
from customers c
left join orders o on o.customer_id = c.id;
customers c
idname
1Ana
2Ben
3Cara
orders o
idcustomer_idtotal
101140
102260
result
nameidtotal
o.customer_id = c.id

Under the left join Cara’s customer row survives and her order columns are null, because no order row exists to supply them. Under the inner join she disappears because only matched pairs survive.

A right join is the same idea with the sides swapped, and a full join keeps the unmatched rows from both tables. Most of the time you want a left join, so write the table you must keep on the left and leave it there.

Patterns

Three shapes carry most outer-join work, in roughly the order you reach for them.

  1. 1
    Keep everyone, count what they have

    Every customer with their order count, zeros included. Count the order key rather than the rows, so a customer with no order counts zero rather than one.

    select c.name, count(o.id) as orders
from customers c
left join orders o on o.customer_id = c.id
group by c.name;
  2. 2
    Find the rows with no match

    Keep every customer, then keep only the ones whose match came back null. That is every customer who never ordered. The shape is common enough to have its own name, the anti-join, and its own guide.

    select c.name
from customers c
left join orders o on o.customer_id = c.id
where o.id is null;
  3. 3
    Keep both sides at once

    A full join keeps the unmatched rows from both tables together: customers with no order and orders with no customer. It is the rarest of the three, worth reaching for only when both absences matter.

Trade-offs

The choice between inner and outer is a choice about what a missing match means. If an unmatched row is noise, an inner join is right and smaller. If the unmatched row is the answer, such as a customer who never ordered, only an outer join can show it. Learning to make that call is most of what this concept asks, because the syntax difference is one word.

Among the outer joins, prefer left and write the preserved table first. A right join reads backwards for no gain, and a full join is useful only when unmatched rows from both sides belong in the answer.

Pitfalls

Every trap here comes from the same source: the null that an unmatched row carries.

A filter in WHERE turns a left join back into an inner join

Add where o.total > 50 and Cara vanishes, because her o.total is null and null is not greater than 50. The left join has quietly become an inner join. When you want to filter the right table but keep the unmatched rows, put the condition in the on clause, where it shapes the match. A condition in where shapes the result instead.

  • Count the key, not the rows. count(o.id) skips the null and gives Cara zero, while count(*) counts her null row as one. The two answer different questions, so pick the one you mean.
  • Null is not a value to compare. Comparing an unmatched row to a value is never true, because its columns are null, not because they fail the test. Reach for is null when absence is the question.
  • Write the kept table on the left. A right join preserves the right table instead. It works, but a query that keeps its far side is needless effort to read.

Performance

An outer join performs the matching work and then retains unmatched rows from the preserved side. Check the relationship key, fan-out, and downstream filters because the word left is not itself the main cost.

Practice

EasyReturn every member with a May checkout count, keeping the date predicate in `on` and counting `checkout_id`. A member with no May activity must remain with zero.MediumBuild a customer anti-list with `left join ... is null` and include both ordered and never-ordered test rows. Only customers with zero matching orders should survive.HardRefactor a recent-orders report so every customer remains by prefiltering the right side or filtering in `on`. Confirm that customers with no recent orders still appear exactly once.

Recap

  • Put the table that must survive on the left, then use a left join to retain all of its rows.
  • Expect nulls where a match is absent and decide what those nulls mean before filtering or counting.
  • Place a right-side filter in on when unmatched rows must remain. Use where when they should be removed.
  • Count a non-null match key when absence should produce zero. count(*) counts the preserved row.
  • Use inner when unmatched rows are irrelevant, or full when absences from both sides matter.

Anti-join

Return the rows in one table that have no match in another.

6 min read

Overview

Some questions are about what is missing.

The products never ordered, the users who never logged in, the payments with no invoice. An anti-join answers them: it returns the rows in one table that have no match in another. You met the shape at the end of the outer joins guide. Here it becomes a tool of its own.

products
idname
1Desk
2Lamp
3Rug
orders
idproduct_id
9011
9021
9032

Trace each product id through orders. Which product survives when the rule is “keep only products with no matching order”?

How it works

An anti-join behaves as a filter over the kept table. For each product, the database probes orders for a match on the key, and a product is removed the moment any match is found. A product whose probe finds nothing is kept. Because no order row is ever attached, the result carries only product columns.

select p.name
from products p
where not exists (
  select 1 from orders o where o.product_id = p.id
)
order by p.id;

Run the demo and compare it with your prediction.

Keep only the unmatchedPress play, or step through it yourself.
products p
idname
1Desk
2Lamp
3Rug
orders o
idproduct_id
9011
9021
9032
result
name
not exists (o.product_id = p.id)

Rug survives because no order carries product_id 3. Desk and Lamp are removed as soon as their first matching order is found.

The same result has two other spellings. A left join whose match came back null, left join orders o on o.product_id = p.id where o.id is null, and not in with a subquery. All three are anti-joins. Trade-offs weighs them below, because one of the three misbehaves around null.

Patterns

The anti-join shows up wherever an absence is the answer.

  1. 1
    The never-matched rows

    Products never ordered, customers who never bought, articles nobody read. Probe the activity table and keep the rows that found nothing. This is the basic negative-existence pattern.

  2. 2
    What one table has that another lacks

    Signups without activations, exports missing from the warehouse. Probe table B from table A and keep the gaps. Run it in both directions and you have a full reconciliation.

    select s.user_id
from signups s
where not exists (
  select 1 from activations a
  where a.user_id = s.user_id
);
  3. 3
    Orphans, by reversing the direction

    Point the probe the other way to audit integrity: orders whose product_id matches no product. On a well-constrained schema the query returns nothing, and an empty answer here means the integrity held.

Trade-offs

Of the three spellings, not exists is the default. It says what you mean, handles null keys correctly, and lets the database stop probing a row at the first match it finds. The left join with is null is just as correct and reads naturally if you think in joins. Databases usually plan both the same way. not in reads the friendliest and is the one to be careful with, for the reason Pitfalls makes plain. Reach for not exists first, and treat the other two as alternative spellings of the same question.

The anti-join has a positive counterpart. The reversed question, which rows do have a match, belongs to the semi-join and its own guide. except subtracts one result from another as well, and the difference is what each compares: except works on entire rows and folds duplicates, while the anti-join filters by a key you choose. The set operations guide covers that boundary.

Pitfalls

NOT IN meets a null and returns nothing

Write where p.id not in (select product_id from orders) and let one product_id be null, and the whole query returns zero rows rather than the unmatched ones. A comparison against null is unknown, so not in can never prove a row is absent from a list that contains one. No error is raised, and the empty result looks like there is nothing to find. This null behavior is why not exists is the safer default.

  • Correlate the probe. The subquery must reference the outer row, o.product_id = p.id. Forget the correlation and you are asking whether orders has any rows at all, which filters everything or nothing.
  • The result is left rows only. An anti-join never returns columns from the probed table. If you find yourself selecting from it, the question was not an anti-join.
  • Absence is direction-sensitive. Products with no orders and orders with no product are different questions. Be deliberate about which table you keep and which you probe.
  • A null key on the kept side stays. A product whose own id is null matches nothing, so not exists keeps it. If null keys are possible on your side, decide whether they belong in the answer and filter them on purpose.

Performance

Logically, a match can reject a row as soon as it is found while an unmatched row requires establishing absence. Engines may use indexes or hashing as part of an anti-join plan. Inspect the probe key and the chosen plan.

Practice

EasyReturn never-ordered products with a null-safe anti-join and alphabetical ordering. Every returned product must have zero order matches, and every ordered product must be absent.MediumBuild a 30-day inactive-customer query with `not exists` and keep the date condition inside the correlated probe. Old-only and never-ordered customers must remain, while recent buyers must not.HardCreate a minimal nullable case where `not in` returns no rows, then rewrite it with `not exists`. The second query must recover valid unmatched rows without being suppressed by null.

Recap

  • Use an anti-join for “A without B,” where the result needs columns from the kept side only.
  • Default to correlated not exists and check that the probe references the current outer row.
  • Guard not in against nullable probe values because one null can suppress every unmatched row.
  • Check direction explicitly, since products without orders and orders without products are different audits.
  • Use a semi-join when presence qualifies the row, or except when whole rows are being subtracted.

Semi-join

Keep the rows that have a match, each one at most once.

6 min read

Overview

Which customers have placed at least one order?

The question asks about customer membership rather than order detail. A semi-join tests whether a match exists without attaching order rows.

customers
idname
1Ana
2Ben
3Cara
orders
idcustomer_id
1011
1021
1032

Trace each customer id into orders. Which customers have at least one match, and how many rows should each contribute?

How it works

For each customer the database probes orders for a match on the key. The first match settles the question because one match proves existence, and the probe moves on to the next customer. Additional matches change nothing, so Ana’s second order never affects her row. No order row is attached at any point. That is why the result contains only customer columns.

select c.name
from customers c
where exists (
  select 1 from orders o where o.customer_id = c.id
)
order by c.id;

Run the demo and compare it with your prediction.

Exists, at most oncePress play, or step through it yourself.
customers c
idname
1Ana
2Ben
3Cara
orders o
idcustomer_id
1011
1021
1032
result
name
exists (o.customer_id = c.id)

Ana and Ben each appear once. Ana’s first matching order proves membership, so her second order cannot create another output row. Cara has no match and is filtered out.

The select 1 inside the probe is a convention rather than a requirement, because exists only asks whether any row comes back and ignores what the probe selects.

Patterns

The three patterns below are the same existence test applied to different tables.

  1. 1
    Filter by activity

    Customers who ordered, users who logged in this week, products with at least one review. Probe the activity table and a row qualifies on its first hit. The time or status condition goes inside the probe so it stays part of the existence test.

    select c.name
from customers c
where exists (
  select 1 from orders o
  where o.customer_id = c.id
    and o.placed_at >= date '2026-06-01'
);
  2. 2
    IN, the same idea by value

    Where the key is a single column, where c.id in (select customer_id from orders) reads well and does the same job. Positive in is safe here. The negative form misbehaves around null, and the anti-join guide covers why.

  3. 3
    Filter first, then aggregate

    When a report should only cover active customers, semi-join first and aggregate after. The filter never multiplies rows and downstream aggregates stay correct.

Trade-offs

The tempting alternative is join plus distinct, which builds every customer-order pair and then collapses the duplicates. It produces the same list but does needless work to get there. The version is also fragile because an aggregate added before the collapse reads the inflated rows. Use a join when you need columns from both tables. Use the existence test when membership is the only question.

Semi and anti are one decision apart, since exists keeps rows with a match and not exists keeps rows without one. The rewrite is one word, but the direction of the question changes completely.

Pitfalls

JOIN plus DISTINCT hides a fan-out

The join-then-dedupe version usually looks equivalent. It stays equivalent until someone adds a count or a sum between the join and the distinct. The duplicates already sit in the rows and the aggregate inflates them, yet the query still completes without error. A semi-join never builds the duplicates, so this failure cannot occur. When distinct exists only to clean up a join, the query is usually answering the wrong question.

  • Correlate the probe. Without o.customer_id = c.id the probe asks whether any order exists at all, and every customer passes or none do.
  • At most once is the definition. If a customer appears twice in your result, the query has drifted into an ordinary join.
  • Keep the condition inside the probe. A recency or status test belongs in the exists subquery. Moved outside it filters a table that does not carry those columns, or forces the join you were avoiding.

Performance

A semi-join can stop logically after the first match, and extra matches do not create output rows. Check the probe key and ensure a row-producing join was not used accidentally.

Practice

EasyCompute city-level premium delivery-fee revenue with `exists`, keeping one row per order before aggregation. Reconcile the total so each qualifying order’s fee contributes once despite multiple premium items.MediumReturn recently reviewed products with the recency predicate inside an `exists` probe. Each qualifying product should appear once regardless of review count.HardReplace a join-plus-distinct filter with a semi-join and compare the pre-distinct row counts. The final parent set must stay unchanged without creating fan-out.

Recap

  • Reach for a semi-join when membership is the question and no columns from the probed table are needed.
  • Use a correlated exists probe and keep time or status conditions inside it.
  • Check that each kept row appears at most once regardless of how many matches exist.
  • Replace join-plus-distinct when the join only created duplicates that the query later removes.
  • Use an anti-join when absence qualifies the row, or an inner join when matched columns belong in the result.

Self join

Join a table to itself to read a relationship that lives inside one table.

6 min read

Overview

A self join joins a table to itself. You reach for it when the rows you need to connect already live in the same table, linked by a column that points from one row to another. The employees table below is the standard case. Each row carries an id, a name, and the manager’s id. A department and a salary complete the row.

employees
idnamemanager_iddeptsalary
1DevinnullExec320
2Maya1Sales150
3Omar1Sales140
4Priya2Eng160

Look at the manager_id column, where Maya’s value is 1 and row 1 belongs to Devin. Her manager is not stored in some other table. The value simply points at another row of the same table. To put every employee next to their manager, you read employees twice and line the two readings up.

How it works

A self join is an ordinary join with one difference: both sides are the same table. Everything you know about joining still holds. You match a column on one side to a column on the other, and the two rows become one. What changes is that the database reads employees twice, so each read needs a name. The aliases e and m are those names. They let you write e.name and m.name without the database asking which copy you meant.

Because it is still a join, you choose its type. Inner keeps only rows that find a match. Left keeps every row on the chosen side, even when nothing matches. That choice is not cosmetic here. It decides whether Devin, who has no manager, survives. Decide what each type should do with him, then toggle the demo and check yourself.

One table, read as twoPress play, or step through it yourself.
select e.name as employee, m.name as manager
from employees e
left join employees m on e.manager_id = m.id;
employees e
idnamemanager_id
1Devinnull
2Maya1
3Omar1
4Priya2
employees m
idname
1Devin
2Maya
3Omar
4Priya
result
empmgr
e.manager_id finds m.id

Under the inner join Devin disappears because manager_id is null and matches no manager row. Under the left join his employee row survives and the manager columns become null.

Patterns

The mechanism stays the same in every use. A table points at its own rows, and you read it twice to bring them together. The three patterns below apply it to situations you will meet in real work, each on a table you can see.

  1. 1
    Find pairs in the same table

    To pair rows that share a value, two employees in the same department, join the table to itself on that value. The condition a.id < b.id does two jobs. It stops a row from pairing with itself, and it returns each pair once instead of both Maya-Omar and Omar-Maya.

    select a.name, b.name, a.dept
from employees a
join employees b
  on a.dept = b.dept
 and a.id < b.id;
    abdept
    MayaOmarSales
  2. 2
    Compare a row to its neighbor

    This pattern uses a new table, monthly_revenue, which holds one row per month with that month’s revenue. To compare each month with the one before it, join the table to itself where one month is one less than the other. A left join keeps the first month, which has nothing before it, with a null change.

    select cur.month, cur.revenue,
       cur.revenue - prev.revenue as change
from monthly_revenue cur
left join monthly_revenue prev
  on prev.month = cur.month - 1;
    monthrevchange
    1100null
    2140+40
    3120-20
  3. 3
    Match on a comparison, not equality

    A match does not have to be equality. To count how many colleagues earn more than each person, join the table to itself where one salary is higher and count the matches: on h.salary > e.salary. This can be expensive because it intentionally creates many pairs.

Trade-offs

The neighbor query also has a shorter form, revenue - lag(revenue) over (order by month), so it is worth knowing when to reach for which. A window function steps along an ordering and reaches a fixed offset from the current row, which covers the previous value as well as running totals and ranks. A self join expresses what an offset cannot, because the relationship between rows can be a key or a range or any condition you can write. The judgment worth practicing is recognizing which relationship you are reading, because the syntax of either tool takes minutes to learn.

Pitfalls

The row that quietly vanishes

An inner join drops every row whose pointer is null. Here that is Devin, the person at the top. The query still returns rows and looks correct, while the person at the top has silently disappeared from the answer. This is the most common self-join bug, so reach for a left join whenever the whole set matters.

  • It reaches one level. A self join gives you a person and their manager, not their manager’s manager. A hierarchy of unknown depth needs a recursive CTE.
  • Drop the condition and you get a cross join. With no on clause, every row pairs with every row. Ten thousand rows become a hundred million.
  • Name collisions need an alias. Reference a shared column without one and the database stops with an ambiguous column error. It cannot know which copy you meant.

Performance

The condition determines whether a self join behaves like a key lookup or creates many pairs. Estimate result cardinality before running comparison-based shapes such as a.id < b.id or h.salary > e.salary.

Practice

EasyBuild an employee-manager roster with two aliases and a left self join. Every employee must appear once, with the top manager retained under a null manager.MediumProduce same-department employee pairs under `a.id < b.id`. The output may contain no self-pairs or reverse duplicates, and no pair may cross departments.HardImplement month-over-month change once with a self join and once with `lag`. Both queries must return identical deltas. State which version is clearer for ordered adjacency.

Recap

  • Use a self join when two related roles or rows live in the same table, then alias each role.
  • Select inner or left deliberately based on whether a null pointer must survive.
  • Order a pair predicate such as a.id < b.id to remove self-pairs and reverse duplicates.
  • A recursive CTE fits when the relationship can span an unknown number of levels.
  • A window function is usually clearer when the task follows a fixed offset in an ordering.
  • Estimate the pair count before running comparison conditions on a large table.

Set operations

Stack results on top of each other instead of joining sideways.

6 min read

Overview

Joins combine tables sideways, attaching columns. Set operations combine two results with the same shape by stacking them into one. No matching happens and no columns attach, because rows simply share a column list and pile up.

june_customers
name
Ana
Ben
july_customers
name
Ben
Cara

Two months of customers hold three different questions. union answers who was active in either month, intersect answers who was active in both, and except answers who was active in June but gone in July. The same two stacks produce three different answers.

How it works

Write two complete queries and put the operator between them. union keeps every distinct row from either side, folding duplicates: Ben appears in both months and lands in the result once. intersect keeps only the rows both sides share. except keeps the left side’s rows minus the right side’s. Toggle the operator and watch the same stacks give three answers.

select name from june_customers
union
select name from july_customers
order by name;

Count the distinct names across the two months before you press play, because that count is exactly what union should return.

Stack, not matchPress play, or step through it yourself.
select name from june_customers
union
select name from july_customers;
june_customers
Ana
Ben
july_customers
Ben
Cara
result
name
same columns, stacked

Each operator has an all form that skips the folding, so union all would keep both Bens. The distinction decides whether you get a list of names or a count that is quietly wrong, and Pitfalls makes it concrete.

Patterns

Set operations are the right tool when the same shape of data lives in more than one place.

  1. 1
    Stack the periods, tag the source

    Monthly extracts, regional tables, this year and last year: union all them into one result and tag each row with a literal column naming its source. Then aggregate the stack as one table.

    select 'june' as month, name from june_customers
union all
select 'july' as month, name from july_customers;
  2. 2
    This, but not that

    When rows were active last month and silent this month, except finds them. It also finds exports present then but missing now. The operator subtracts one result from the other and hands you churn or regressions or gaps.

  3. 3
    The overlap

    Customers retained across both months, users in both cohorts: intersect keeps exactly the rows the two sides agree on. It reads like the retention question it answers.

Trade-offs

Default to union all and promote to union only when you can say what duplicates you are removing and why. The bare union reads friendlier, but its dedupe is both a cost and a decision about your data, and it should be made on purpose.

except and the anti-join both answer "in A but not in B", and they draw the line differently. except compares whole rows and folds duplicates. The anti-join probes by a key you choose and keeps the left rows as they are. Reach for except when the results are the same shape and row-level identity is the question. Reach for the anti-join when a key defines the match, which in real schemas is most of the time.

Pitfalls

UNION folds duplicates you meant to keep

Stack two months of order rows with union and any rows identical across the stack collapse into one. A repeated customer and amount simply vanishes from the total. The sums come out lower and no error is raised, so the query looks tidy while lying. When the stacked rows are facts rather than a membership list, union all is almost always the one you meant.

  • Columns align by position, not name. The stacks are zipped column by column in order. Same count, compatible types, and the names come from the first query. A swapped column pair fails silently if the types happen to agree.
  • except is direction-sensitive. June except July is who left, while July except June is who arrived, so read the order out loud before you trust the result.
  • One order by, at the end. The sort applies to the finished stack rather than to either input, so sorting the pieces separately has no effect on the final order.

Performance

The cost model here is linear because there is no matching step and no row product. union all is the cheapest combination in SQL, a pure concatenation costing what the inputs cost. The folding forms add a real pass. Deciding what is duplicate or shared means comparing rows across the whole stack, usually by sorting or hashing once. On big stacks that comparison dominates the cost and makes union all the natural default.

Practice

EasyCombine two months of active people with `union` after aligning the projected columns. Each person active in either month should appear exactly once.MediumProduce June-minus-July and July-minus-June result sets with `except`. Keep operand order explicit, then confirm each exclusive customer appears in the correct direction and shared customers appear in neither.HardStack regional order facts with `union all` and compare its row count and revenue with `union`. The `union all` result must preserve every source row and source total.

Recap

  • Use set operations when same-shape results should stack. Use a join when related columns must sit side by side.
  • Use union all to preserve rows and union to deduplicate. Use intersect for overlap and except for left-minus-right.
  • Check that column counts and types are compatible. Then verify positional alignment and use one final order by for the whole stack.
  • Prefer an anti-join for keyed “in A without B” questions when a correlated probe states the intent more directly.
  • Pay for duplicate folding only when the result actually requires it.

Cross join

Every combination of two tables, on purpose.

5 min read

Overview

Every join so far has matched rows. The cross join is the one that does not: it pairs every row of one table with every row of the other, no condition, no key. When it happens by accident it is a bug, and when you ask for it on purpose it is how you build a grid.

sizes
size
S
M
colors
color
Red
Blue

A shop sells shirts in two sizes and two colors. List the combinations and predict how many catalog rows the two tables create.

How it works

With no condition to satisfy, there is nothing to match and nothing to drop. Each row on the left pairs with every row on the right. Keep your combination list and predicted count beside the query. The demo will let you verify both.

select s.size, c.color
from sizes s
cross join colors c
order by s.size, c.color;

Press play and check your list and row count.

Every pair, on purposePress play, or step through it yourself.2 × 2 =
sizes s
size
S
M
colors c
color
Red
Blue
result
sizecolor
no condition: every row pairs

The result has four rows because each of the two sizes pairs with each of the two colors. Nothing is filtered or matched by a key, so the output count is the product of the input counts.

You will also meet the older spelling, a bare comma: from sizes s, colors c. The comma form is the same cross join without the explicit keyword, and most accidental products in old code start exactly there.

Patterns

A deliberate cross join is almost always building a scaffold.

  1. 1
    Generate a grid

    Every variant a catalog should carry, every test case a matrix should cover. Crossing the option tables produces the grid directly, one row per combination.

  2. 2
    Scaffold first, then find the gaps

    Every store crossed with every day gives the rows that SHOULD exist. Left join actual sales onto that scaffold and the null rows are the days a store sold nothing, rows no plain join could show you because they are not in the data. The scaffold pairs naturally with the outer join.

    select st.name, d.day, s.total
from stores st
cross join days d
left join sales s
  on s.store_id = st.id and s.day = d.day;
  3. 3
    Attach a constant row

    Cross join a one-row table of parameters and every row gets those values attached. A computed date works the same way, since one row times n rows is still n rows and nothing multiplies.

Trade-offs

The real choice is between combination and matching rather than between join types. If a key relates the tables, join on it. If the point is every pairing, write cross join explicitly rather than a bare comma. The keyword tells the next reader the product is intended, and old-style comma lists are where forgotten conditions hide. Keep the sides small on purpose, because the product grows faster than intuition expects.

Pitfalls

The accidental product

Forget the on clause, or list tables with commas and forget the where, and every row silently pairs with every row. Three orders and three customers become nine rows, which still looks plausible. A thousand of each becomes a million, and the sums come out wrong while still looking believable. When a result has more rows than the tables that fed it, suspect an unintended cross join before anything else.

  • The product grows fast. A thousand rows on each side is a million pairs, and a million on each side is a trillion, which no plan can make affordable. Know both row counts before you multiply them.
  • The comma spelling hides intent. The reader of from a, b cannot tell a deliberate product from a forgotten condition. Write cross join when you mean it.
  • Duplicates multiply too. A scaffold built from tables with repeated rows repeats its combinations. Feed the cross join distinct sides.

Performance

A cross join must emit n times m rows. Multiply the input counts first, then reduce or deduplicate the inputs when the product is too large.

Practice

EasyGenerate every `sizes` and `colors` combination with an explicit `cross join`. Confirm that the result contains exactly `size_count` times `color_count` unique pairs.MediumCreate a store-day scaffold for a fixed date range and left join daily sales without dropping empty cells. Each store should have one row per day with zero for no sales.HardRepair a report whose totals tripled by restoring the missing join condition. Compare row counts before and after. Each fact must match only its intended dimension row.

Recap

  • Reserve a cross join for cases where every combination is the intended result.
  • Predict n times m rows before running it and reduce the inputs when that product is unsafe.
  • Write cross join explicitly so a reviewer can distinguish intent from a missing condition.
  • Check each input for duplicates because repeated values multiply repeated combinations.
  • Return to a keyed join when a relationship key exists, and suspect a missing condition when rows explode.

Non-equi join

Match rows by a comparison or a range instead of a key.

5 min read

Overview

Every join so far has asked whether two values are equal. A non-equi join asks something looser. Is this amount inside that range, is this date after that one, is this value larger. The join condition is a comparison and the match is whatever satisfies it.

sales
idamount
130
275
3120
tiers
tierlohi
Basic049
Mid5099
Premium100999

Trace 30, 75, and 120 against the tier bounds. Write down the three placements before reading on.

How it works

The mechanics are an ordinary join with the condition swapped: instead of on a.key = b.key, the match is on s.amount between t.lo and t.hi. Each sale pairs with every tier whose test it passes.

select s.id, s.amount, t.tier
from sales s
join tiers t
  on s.amount between t.lo and t.hi
order by s.id;

Run the demo and compare it with your three placements.

Match by range, not keyPress play, or step through it yourself.
sales s
idamount
130
275
3120
tiers t
tierlohi
Basic049
Mid5099
Premium100999
result
idamounttier
s.amount between t.lo and t.hi

Each sale appears once because these integer bands cover the values without overlap. The comparison condition creates the relationship by testing which band contains each amount.

Patterns

Three relationships that only a comparison can express.

  1. 1
    Banding

    Price tiers, grade boundaries, cohort buckets. The bands live in a table and each fact lands in the band that contains it. Changing a boundary is an update rather than a code change.

  2. 2
    Time windows

    Match an event to the session or promotion or shift that was active when it happened, where the event time falls between the window start and end. Time rarely joins on equality, because an event belongs to whatever window contains it.

    select e.id, w.name
from events e
join windows w
  on e.at >= w.starts_at
 and e.at <  w.ends_at;
  3. 3
    Ordered comparisons

    Pair each row with the rows before it, or above it: a.id < b.id, h.salary > e.salary. You met these shapes in the self join guide. The inequality is what makes them work.

Trade-offs

Banding has a rival in the case expression that hardcodes the boundaries in the query. It works, yet it buries the bands where no one can see or change them. A tier table makes the bands data. They become editable and auditable and shared by every query that needs them. Prefer the table, because making the bands data is the instinct the modeling track teaches.

Keep equality in the condition where it exists. Matching sales to tiers within one region is on s.region_id = t.region_id and s.amount between t.lo and t.hi, where the equality narrows the candidates and the range completes the match. A non-equi join adds comparisons to keys rather than replacing them.

Pitfalls

Overlapping bands bring the fan-out back

If one band ends at 50 and the next begins at 50, a sale of exactly 50 passes both tests and appears twice. Every count downstream then inflates. Bands must tile, meaning they touch without overlapping and cover without gaps. The convention that guarantees it is a closed lower bound and an open upper one. Write it lo <= amount and amount < hi and set each band’s hi to the next band’s lo.

  • between is inclusive on both ends. A band written 0 to 50 and a band written 50 to 100 both claim 50. Decide which end is open and write the comparison out when it matters.
  • Gaps drop rows silently. An amount no band contains simply vanishes under an inner join, exactly like any unmatched row. A left join against the bands shows you what fell through.
  • One row can legitimately match many. The ordered-comparison shapes are supposed to fan out, but a sale-to-tier match is not, so know which of the two you are writing.

Performance

Comparison joins can cost more than selective equality joins. Their cost depends on the predicates and data distribution as well as the plan. Retain available equality predicates to narrow the candidates, then inspect the plan when both sides are large.

Practice

EasyLabel sales through a range join whose tier bands do not overlap. Every sale must appear exactly once with the tier whose bounds contain its amount.MediumMatch events to promotion windows with half-open time bounds and the shared business key. Each event should match at most one promotion. An event at `starts_at` must match while one at `ends_at` must not.HardRepair a banding join by converting its ranges to contiguous half-open intervals. A boundary test suite must yield one match per covered amount and zero matches outside.

Recap

  • Use a non-equi join when the relationship is a range, time window, or ordered comparison.
  • Use half-open bands and check both gaps and overlaps with boundary values.
  • Retain equality predicates such as a business key so fewer rows need the comparison test.
  • A left join audit exposes unmatched values that inner semantics would drop silently.
  • Inspect the plan when both sides are large because comparison cost depends on data and predicates.
That is every join.

Inner, outer, self, semi, anti, cross, non-equi, and the set operations that stack results instead of matching them. You now hold the whole family. Most real queries are these joins combined with the filtering and grouping you already know, so from here the hard part is rarely the join itself. It is choosing the right one, which you can now do.

Put the joins to work