Core CS · DBMS

Twelve pairs, and the three you keep

Q: NATURAL JOIN, USING, or ON. Which do you write?

ON , almost always. NATURAL JOIN joins on every column name the two tables happen to share, so its meaning is decided by the schema rather than by you: add a column called name to the second table and the query silently starts joining on two columns and returns nothing. USING (did) is safe because you name the column and it collapses the two into one, but it needs identical names and can only mean equality. ON says what you meant and keeps saying it after somebody else edits the tables.

A join does not go and fetch matching rows. It considers every pairing of a row from one table with a row from the other, and returns the pairings your condition calls true. Once you can see the pairings, INNER, LEFT, RIGHT and FULL stop being four things to memorise.

Step through all four joins on the same rows →

Four students, three departments, twelve possible pairings, and the condition is true for three of them. Every join type in SQL agrees on those three. They differ only in what they do with the one student who matched nothing and the one department that matched nothing.

01 The idea

The data was split on purpose

Good schema design pulls one wide table apart into several narrow ones so that each fact is stored exactly once. A student’s department is recorded as a short code in the student row, and the readable department name is recorded once in a department row. That is the right thing to do for writing: change the name of a department and you change one row, not five hundred.

It leaves you with a reading problem. No single table can answer “which department is Meera in”, because the answer is half in one table and half in the other. A join is how you put the two halves back together for one query, without ever putting them back together on disk. You name two tables and a condition, and you get one result table back.

What the database does with that is worth taking literally, because every other question about joins falls out of it. It considers every pairing of a row from the first table with a row from the second. Four student rows and three department rows are twelve pairings. It tests your condition once per pairing and keeps the pairings where the condition came out true. That is an INNER JOIN, and it is the whole mechanism.

Real databases do not literally build all twelve and throw nine away. A query planner picks a hash join or a merge join and touches far fewer rows than that. But the twelve pairings are the definition of the answer, and the planner is only allowed to be clever as long as it returns exactly what the definition says. So when a result surprises you, go back to the pairings. They are always right.

A join is a filter over every possible pairing of two tables. INNER keeps the pairings the condition calls true. The outer joins keep those too, and then add back the rows that were in no true pairing, padded with NULL where the other table has nothing to give.

Cartesian productEvery row of one table paired with every row of the other. Four student rows and three department rows give twelve pairs. It is where every join starts, and it is what CROSS JOIN hands back on its own, because that is the one join form with no condition to test.

Join conditionThe predicate written in ON. It is tested once per pairing, and only pairings where it comes out true survive. Pairings where it is false, and pairings where it is unknown because a NULL was involved, are both discarded.

NULL-extended rowThe row an outer join adds for a row that matched nothing. Every column belonging to the table that had no matching row is filled with NULL, because there is no value to put there. It is the only kind of result row that was in no pairing at all.

02 Worked example

Four students, three departments, twelve pairings

Here are the two tables this whole lesson runs on. They are deliberately small enough to check by hand, and they are deliberately untidy in two specific ways: one student has not been assigned a department yet, and one department has no students in it. Those two rows are where all the interesting behaviour lives.

student                                deptsid   name    did     mentor           did   dnameS1    Asha    D1      NULL             D1    CSES2    Ravi    D2      S1               D2    ECES3    Meera   D2      S1               D3    MECHS4    Kiran   NULL    S2 -- Kiran has no department. MECH has no students. Both are real states.-- mentor points at another sid in this same table; it is used in section 03.

The condition we will use everywhere is s.did = d.did. Follow the flow left to right and watch where the row count comes from at each step.

1 · Two tables4 student rows, 3 dept rows. Neither one can answer “which department is Meera in” on its own

→

2 · Every pairing4 × 3 = 12 candidate rows, the Cartesian product, and also exactly what CROSS JOIN returns

→

3 · Test the conditions.did = d.did on each of the 12. True for 3. Kiran’s did is NULL, so his 3 pairings are unknown, not true

→

4 · INNER stops here3 rows: Asha CSE, Ravi ECE, Meera ECE. Kiran and MECH are gone, with nothing to say they existed

→

5 · OUTER adds them backLEFT adds Kiran with NULLs, giving 4. RIGHT adds MECH, giving 4. FULL adds both, giving 5

Node 3 is where this topic is won or lost, so read it slowly. Kiran’s did is NULL, and NULL = ‘D1’ does not come out false. It comes out unknown, which is a third value SQL comparisons are allowed to return whenever a NULL is involved. A join keeps only the pairings its condition calls true, and unknown is discarded in exactly the same way false is. So Kiran is in no true pairing, and no amount of adding departments will ever change that.

Node 4 hides the other surprise. Both Ravi and Meera have did = ‘D2’, so the ECE department row appears in two of the three surviving pairings. A join is not a lookup that returns one row per row. One row on one side produces one result row per matching row on the other side: none, one, or many.

Everything in the next section is a decision about node 5. Which of the two leftover rows do you want back, and what goes in the columns the other table cannot fill.

03 Mechanics

Five join types over the same twelve pairings

One row per join type, same two tables, same condition s.did = d.did. The third column is the exact result on our data, hand-counted, and the last column is what actually goes wrong with each one in practice. The four that take an ON clause all test the same 12 pairings and all find the same 3 true ones. CROSS JOIN is the odd one out: it has no condition to test, so it hands back all 12.

Join	What it keeps	Result on our four students and three departments	Rows	What goes wrong with it
CROSS JOIN	Every pairing. There is no condition at all.	All 12 pairs, including Kiran paired with each of the three departments.	`12`	Almost always an accident. Two tables listed in FROM separated by a comma, with the join condition missing from the WHERE, is a cross join, and the row count is the product of the two table sizes.
INNER JOIN … ON	Only the pairings where ON comes out true.	Asha CSE, Ravi ECE, Meera ECE.	`3`	Rows that matched nothing vanish from both sides with no warning. The word INNER is optional, so a bare JOIN is this one.
LEFT OUTER JOIN	All of INNER, plus every left row in no true pairing, with the right table’s columns set to NULL.	The same 3, plus Kiran with did and dname NULL.	`4`	The left table’s row count is a floor, not a ceiling. Ravi and Meera both matched D2, so a left row can still fan out into several.
RIGHT OUTER JOIN	All of INNER, plus every right row in no true pairing, with the left table’s columns set to NULL.	The same 3, plus MECH with sid and name NULL.	`4`	It is a LEFT JOIN with the two table names swapped, and nothing else. Mixing both directions in one query makes a chain of joins very hard to read.
FULL OUTER JOIN	All of INNER, plus the unmatched rows from both sides.	The same 3, plus Kiran, plus MECH.	`5`	The only join type where a result row can be NULL on either side, and the only one that is not available everywhere. It is the honest answer to “show me everything from both tables and line up what matches”.

There are three ways to write the condition itself, and only one of them keeps meaning what you wrote after somebody else edits the tables.

Form	Written out	What it returns today	Why it is or is not safe
ON	student s JOIN dept d ON s.did = d.did	3 rows, and SELECT * shows both s.did and d.did.	Explicit. The condition is written down in the query, so adding or renaming a column in either table cannot silently change what the query means. It is also the only form that takes a condition other than equality.
USING	student JOIN dept USING (did)	The same 3 rows, but did appears once instead of twice.	Safe, because you name the column yourself. It only works when the column has the same name in both tables, and it can only ever mean equality.
NATURAL JOIN	student NATURAL JOIN dept	The same 3 rows, today.	It guesses. It joins on every column name the two tables happen to share, so the schema decides what your query means. Add a name column to dept and this query silently starts joining on did and name together, which on our rows matches nothing. Rename dept.did and the two tables share no column at all, which in standard SQL makes it a cross join returning 12.

Two more shapes you are expected to recognise on sight. A self join is a table joined to itself, which is what you need when a row points at another row of the same table. An anti-join is not a keyword at all: it is a LEFT JOIN plus an IS NULL test, and it is the standard way to ask “which rows matched nothing”.

-- self join: one table, two aliases. The aliases are not a style choice.SELECT s.name AS student, m.name AS mentorFROM   student sJOIN   student m ON s.mentor = m.sid;       -- 3 rows. Asha's mentor is NULL, so INNER JOIN drops her. -- anti-join: the left rows that were in no true pairing, and only thoseSELECT s.sid, s.nameFROM   student sLEFT JOIN dept d ON s.did = d.didWHERE  d.did IS NULL;            -- 1 row: S4 Kiran -- same shape, other direction: departments with no studentsSELECT d.did, d.dnameFROM   dept dLEFT JOIN student s ON s.did = d.didWHERE  s.sid IS NULL;            -- 1 row: D3 MECH

In the self join, s and m are two independent copies of the same four rows, and without them sid would be ambiguous with no way to say which copy you meant. In the anti-join, the trick is that only NULL-extended rows can have a NULL in d.did, so testing that one column for NULL isolates exactly the rows that matched nothing. Test a column that could legitimately be NULL in a real row and you will throw away rows that did match.

The last shape is the one that costs people offers. The same extra test placed in ON and in WHERE gives two different answers on an outer join.

-- (A) the extra test is part of the join conditionSELECT s.name, d.dnameFROM   student sLEFT JOIN dept d ON s.did = d.did AND d.dname = 'ECE';-- 4 rows:   Asha NULL | Ravi ECE | Meera ECE | Kiran NULL -- (B) the extra test is a filter on the finished joinSELECT s.name, d.dnameFROM   student sLEFT JOIN dept d ON s.did = d.didWHERE  d.dname = 'ECE';-- 2 rows:   Ravi ECE | Meera ECE

In (A) the extra test runs while the pairings are being judged. Asha’s only candidate is the CSE row, and ‘CSE’ = ‘ECE’ is false, so she ends up in no true pairing at all. LEFT JOIN then does what LEFT JOIN always does and gives her a row anyway, with NULLs. All four students survive.

In (B) the join finishes first, and its ON carries no extra test, so it produces the plain LEFT JOIN result: Asha with CSE, Ravi with ECE, Meera with ECE, and Kiran with NULLs. Four rows again, but not the same four. Only then does WHERE start deleting from that result. Asha’s row holds dname = ‘CSE’, which is not ‘ECE’, so it goes. Kiran’s row holds dname = NULL, and NULL = ‘ECE’ is unknown rather than true, so it goes too. What survives is the inner-join answer with a filter on it. On an INNER JOIN the two placements are interchangeable and nobody can tell the difference. On an outer join they are not.

ON decides which pairings exist. WHERE deletes rows from the result after the join has finished. Any WHERE comparison against a column from the optional table also deletes the NULL-extended rows, which quietly turns your outer join back into an inner one. IS NULL is the one test that does the opposite and keeps only those rows, which is exactly why the anti-join is written that way.

05 Cheat sheet

Every join type on one card

The row counts are for the lesson’s four students and three departments joined on s.did = d.did. Learn the counts with the reason attached, because the number on its own proves nothing.

Join	What it keeps	Rows here	The one thing to remember
CROSS JOIN	every pairing, no condition	`12`	4 × 3. A comma between two tables in FROM with no WHERE is this, by accident.
INNER JOIN	pairings where ON is true	`3`	Rows that matched nothing disappear from both sides, silently. A bare JOIN means this.
LEFT OUTER JOIN	INNER, plus unmatched left rows, right columns NULL	`4`	Kiran survives with NULLs. Use it when the absence of a match is itself an answer.
RIGHT OUTER JOIN	INNER, plus unmatched right rows, left columns NULL	`4`	Identical to a LEFT JOIN with the table names swapped. It buys you nothing.
FULL OUTER JOIN	INNER, plus the unmatched rows from both sides	`5`	The only one where a row can be NULL on either side. MySQL does not have it, so you assemble it out of the joins MySQL does have.
Self join	a table joined to itself on s.mentor = m.sid	`3`	Not a keyword. It is two aliases on one table name, and the aliases are compulsory.
Anti-join	left rows that matched nothing	`1`	Not a keyword either. LEFT JOIN plus WHERE d.key IS NULL, tested on a column that is never NULL in a real row.

NULL never equals NULLWhenever either side of s.did = d.did is NULL the comparison is unknown, not false, and a join keeps only what the condition calls true. So Kiran matches no department and never will, and two rows that both have NULL in the join column do not match each other. The same rule reaches counting: COUNT(*) counts rows, while COUNT(col) counts only the values of col that are not NULL, so over a join result the two are different numbers. It is also why NOT IN against a subquery whose result contains even one NULL returns no rows at all, whatever you were looking for, and why an anti-join is written with LEFT JOIN … IS NULL or NOT EXISTS instead.

A join can grow the resultFour students and three departments gave three rows here, but ECE was matched twice because two students are in it. Each left row emits one result row per matching right row: none, one, or many. Joining on the other table’s primary key is the only thing that caps it at one. When a monthly report suddenly doubles its totals, the usual cause is a join key that stopped being unique.

Write LEFT, and name the driving table firstA RIGHT JOIN B is B LEFT JOIN A with the names swapped, so RIGHT buys you nothing and costs the next reader a re-read. Decide which table you must not lose rows from, name it first, and LEFT JOIN everything onto it. A chain of four joins then has one reading rule instead of four.

06 Where & why

What your database actually does with a join

The twelve pairings are the definition of the answer, not the plan. No mainstream database forms them and then discards nine. What differs between real systems is which join types you are allowed to type at all, and what the engine does underneath, and interviewers ask about both.

PostgreSQL

All five, and it will show you the plan

INNER, LEFT, RIGHT, FULL and CROSS, plus USING and NATURAL. Put EXPLAIN in front of your query and it names the strategy it chose: Nested Loop, Hash Join or Merge Join. A hash join builds a small hash table on one side and probes it once per row of the other, so the twelve pairings in this lesson are never formed. The answer is identical either way, which is the point: the pairings define the result, the plan only has to agree with it.

MySQL

Four of the five, and no FULL OUTER JOIN

Everything except FULL OUTER JOIN, which you have to assemble out of the join types MySQL does have. The trap in assembling it is that the matched rows are produced by both halves, so a careless version reports them twice. MySQL also treats JOIN, INNER JOIN and CROSS JOIN as synonyms in FROM, so a CROSS JOIN b ON … is accepted there and rejected by the standard.

Oracle

Two syntaxes, and the old one is still in the wild

Oracle supports the standard syntax in full, but legacy code is full of its older (+) operator: WHERE s.did = d.did(+) is a LEFT JOIN keeping all students. The (+) marks the side that is allowed to be missing and will supply the NULLs, which is the opposite of what most people guess. It also cannot be used with OR or IN, and cannot be mixed with standard join syntax in the same query, so converting old code is rarely a straight substitution.

SQLite · SQL Server

The two that are missing pieces

SQLite has had INNER, LEFT, CROSS, NATURAL and USING for years, but RIGHT JOIN and FULL OUTER JOIN only arrived in version 3.39 in 2022, and plenty of Android and embedded builds still ship something older. SQL Server has FULL OUTER JOIN but no NATURAL JOIN and no USING. Between them they are the practical argument for writing explicit ON conditions and LEFT joins by default.

Every one of these returns the same answer to the same query, because the answer is defined by the pairings and the condition rather than by the plan. What actually varies is which join types you are allowed to type, and the safe set everywhere is INNER JOIN and LEFT JOIN with an explicit ON, plus a bare CROSS JOIN, which takes no ON at all.

07 Interview questions

What they actually ask

Joins are the most reliably asked SQL topic in a placement interview, and the questions almost always start easy and then go straight at NULLs and row counts. Answer with numbers from a concrete pair of tables rather than with definitions.

What is a join, in one sentence?

Two tables, every pairing of their rows considered, and the pairings the condition calls true returned as result rows. With four students and three departments that is twelve pairings, and s.did = d.did is true for three of them, so INNER JOIN returns three rows. Every other join type returns those same three plus a decision about the rows that were in no true pairing.

Does the database actually build the Cartesian product?

No, and it is worth saying so because the definition sounds wasteful. The pairings define the answer; they are not the plan. A planner picks a nested loop, a hash join or a merge join, and a hash join builds a small hash table on one side and probes it once per row of the other. EXPLAIN shows you which one it chose. The result is identical either way, so reasoning about pairings is always safe even though the machine never forms them.

What is the difference between an INNER JOIN and a LEFT OUTER JOIN?

INNER returns only the pairings where ON is true. LEFT returns those, and then adds back every row of the left table that was in no true pairing, with the right table’s columns set to NULL. On our data INNER gives 3 rows and LEFT gives 4, and the extra row is the student with no department. Reach for LEFT when the absence of a match is itself part of the answer you want.

A row has NULL in the join column. Which rows does it match?

None, ever. NULL = ‘D1’ does not evaluate to false, it evaluates to unknown, and a join keeps only pairings the condition calls true. Unknown is discarded in exactly the same way false is. So a student with a NULL department is in no true pairing, disappears under INNER, and comes back under LEFT as a NULL-extended row. Two rows that both have NULL in the join column do not match each other either.

Can a join return more rows than either table has?

Yes, and it is the most common surprise in a report. Each left row emits one result row per matching right row, so a left row can produce none, one, or many. In our data two students share department D2, so the ECE row appears twice in a three-row result. Joining on the other table’s primary key is the only thing that caps it at one; if a report’s totals suddenly double, look for a join key that stopped being unique.

What is a self join, and why does it need aliases?

A table joined to itself, which is what you need when a row points at another row of the same table. Our student table has a mentor column holding another student’s sid, so student s JOIN student m ON s.mentor = m.sid puts the mentor’s name beside the student’s. The aliases are not style: without them sid and name are ambiguous and there is no way to say which of the two copies you meant.

How do you find the rows in one table that have no match in the other?

LEFT JOIN the second table, then WHERE one of its columns IS NULL. Only the NULL-extended rows survive that test, and those are exactly the rows that matched nothing. Pick a column that can never be NULL in a genuine row, normally the primary key, or you will also throw away rows that did match and happened to have a NULL there. This shape is usually called an anti-join, though it is not a keyword.

NATURAL JOIN, USING, or ON. Which do you write?

ON, almost always. NATURAL JOIN joins on every column name the two tables happen to share, so its meaning is decided by the schema rather than by you: add a column called name to the second table and the query silently starts joining on two columns and returns nothing. USING (did) is safe because you name the column and it collapses the two into one, but it needs identical names and can only mean equality. ON says what you meant and keeps saying it after somebody else edits the tables.

Your LEFT JOIN returned exactly the INNER JOIN rows. What happened?

Almost certainly a WHERE test on a column from the right-hand table. The join produces the NULL-extended rows correctly, and then WHERE tests them: d.dname = ‘ECE’ against a NULL is unknown rather than true, so the row is deleted and the inner result is what survives. If the test belongs to the join, move it into the ON. If it really is a filter, keep it in WHERE and accept that you wanted an inner join.

Is a RIGHT JOIN ever necessary?

No. A RIGHT JOIN B is B LEFT JOIN A with the two names swapped and the same condition, so it never buys you a row you could not get otherwise. Most teams write only LEFT, so a chain of joins always reads as “keep everything from the table named at the top”. It matters practically too: RIGHT JOIN was a syntax error on SQLite before version 3.39 in 2022, and older embedded builds are still around.

Which join types are not available everywhere?

FULL OUTER JOIN is the one to remember. PostgreSQL, Oracle and SQL Server have it, MySQL does not, and SQLite only gained it in 3.39 in 2022 along with RIGHT JOIN. NATURAL JOIN and USING are absent from SQL Server. INNER JOIN and LEFT JOIN written with an explicit ON, plus a bare CROSS JOIN with no condition on it, are what you can type anywhere without checking the manual first.

Have you ever written a CROSS JOIN on purpose?

Rarely, and always against something small. Generating one row per month-and-region pair so a report has a slot for combinations with no data, or attaching a single-row settings table to every row of a query, are the two normal reasons. Far more often a cross join is an accident: two tables listed in FROM with a comma and the condition missing from the WHERE. It is easy to spot, because the row count comes out as the product of the two table sizes.

08 Practice problems

Six to work through

Every one of these uses the four students and three departments from section 02. Write the pairings out on paper before you write any SQL. Deriving a row count by reasoning about what the query “should” return is exactly the habit these problems are built to break.

Students who share a mentor

Easy

The mentor column holds another student’s sid: Asha’s is NULL, Ravi’s and Meera’s are S1, Kiran’s is S2. Using only the student table, write the query that returns every pair of students who have the same mentor, and write down the exact rows it returns. Then give the rows again after you add a.sid <> b.sid.

Follow-up

The plain condition a.mentor = b.mentor returns pairs you did not want in two different ways, and a.sid <> b.sid only removes one of them. One student is missing from every version, and no change you can make to the condition will bring her back.

Show the hint

Write out all sixteen pairings of the four rows with themselves first, then test the condition on each pairing one at a time.

Read the NULLs backwards

Easy

Follow-up

One of the three comes back from all five, including the one that has no condition at all. The other two come back from exactly two each, and neither of those two rows can ever come out of a CROSS JOIN.

Show the hint

A NULL-extended row tells you which table the join was told to protect. Start from that table rather than from the NULLs.

Move the predicate

Medium

Take student s LEFT JOIN dept d ON s.did = d.did and add the test s.name <> ‘Ravi’ in three places: (a) inside the ON; (b) in a WHERE after the join; (c) inside the ON of the equivalent INNER JOIN. Give the exact rows returned in each case, with both columns.

Follow-up

Version (a) does not remove Ravi from the result at all, and the three versions return three different row counts. This is a test on the left table, which behaves differently from the right-table test worked through in section 03.

Show the hint

The ON condition decides whether a pairing survives, not whether a left row survives. Ask what a LEFT JOIN does with a left row whose pairings have all failed.

The count that lies

Medium

A colleague reports: “there are 3 departments, and SELECT COUNT(*) FROM student s JOIN dept d ON s.did = d.did says 3, so every department has exactly one student.” Say what that 3 is actually counting. Then write the query that returns each department’s name beside its number of students, including departments that have none, grouping with GROUP BY d.dname, and give its result on this data.

Follow-up

One department’s number comes out wrong if you count rows, and it is the department that has nothing to count. Getting it right does not need a different join.

Show the hint

Start from the table you must not lose rows from, and write out every row the join produces before you group anything.

Two joins, one query

Medium

Add a third table hostel(did, warden) with exactly one row, (‘D2’, ‘Mr Rao’). Write one query listing every student with their department name and their warden, keeping students who have no department, and give its result. Then say exactly what changes if the second join is written as an INNER JOIN.

Follow-up

The second join is applied to the result of the first, not to student, so it also has to cope with rows where d.did is already NULL. An inner join later in a chain throws away the very rows an earlier LEFT JOIN went out of its way to keep.

Show the hint

Work out the four rows the first LEFT JOIN produces, then treat those four rows as the left table for the second join.

Build FULL OUTER JOIN out of parts

Hard

You are on MySQL, where FULL OUTER JOIN does not exist. Using only join types MySQL has, plus UNION or UNION ALL, write a query over student and dept returning exactly the rows a FULL OUTER JOIN … ON s.did = d.did would. List the rows each half produces, say how many rows the wrong set operator gives, and name which of those rows are wrong. Then write a second version that uses UNION ALL and is still exactly right.

Follow-up

The UNION version is correct here only because all five rows of the answer happen to be distinct. UNION deduplicates whole rows, so drop s.sid from the SELECT list and give two students the same name in the same department, and it deletes a row a real FULL OUTER JOIN would return. That is why the UNION ALL version is the one you can trust on any data.

Show the hint

For the second version, make one half contribute only the rows the other half is structurally incapable of producing, and nothing else.