SQL Joins: Combining Data from Multiple Tables

Picture the school library register. On one page, the librarian keeps an Admission Register: roll number, student name, section. On a separate page, she keeps a Borrowing Register: a serial number, the roll number of whoever borrowed a book, and the book's title. Now the principal asks: "Give me a list of which student borrowed which book, by name." The librarian cannot answer by reading either register alone. She has to flip between both pages, find matching roll numbers, and write the name next to the book title. That manual matching-by-a-common-column is exactly what a SQL JOIN does — except a database does it for millions of rows in a fraction of a second, and you tell it how using one query.

This chapter builds joins from the ground up: why data lives in separate tables in the first place, what actually happens mechanically when two tables are combined, and how to write the three SQL statements that matter most — the Cartesian product, the equi-join, and the JOIN...ON syntax used in real databases, including what happens to rows that don't have a match.

Why split data into two tables at all?

A tempting shortcut is to keep one giant table: StudentID, Name, Section, BookTitle, BorrowDate — all in one place. But think about what happens the moment a student borrows a second book. You'd have to repeat their name and section on a new row. Borrow five books, and "Aditi, 9A" is typed out five times. If Aditi later shifts from Section 9A to 9C, you now have to hunt down and fix five rows instead of one. This repetition is called data redundancy, and it's a breeding ground for inconsistency — one row says "9A", another still says "9C" after a sloppy update, and now nobody knows which is correct.

The fix is to store each fact exactly once, in the table it belongs to, and link tables using a shared column. This is the core idea behind relational databases, and it's why joins exist: you get the safety of separated data, plus the ability to bring it back together on demand.

The sample database for this chapter

We'll use two small tables and trace every example against them by hand, so you can see precisely what the database engine is doing — not just trust the output.

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(20),
  Section VARCHAR(5)
);

INSERT INTO Students VALUES (1, 'Aditi', '9A');
INSERT INTO Students VALUES (2, 'Rohan', '9B');
INSERT INTO Students VALUES (3, 'Meera', '9A');
INSERT INTO Students VALUES (4, 'Kabir', '9C');

CREATE TABLE BorrowedBooks (
  BorrowID INT PRIMARY KEY,
  StudentID INT,
  BookTitle VARCHAR(40)
);

INSERT INTO BorrowedBooks VALUES (101, 1, 'Panchatantra');
INSERT INTO BorrowedBooks VALUES (102, 3, 'Discovery of India');
INSERT INTO BorrowedBooks VALUES (103, 1, 'Malgudi Days');
INSERT INTO BorrowedBooks VALUES (104, 5, 'Wings of Fire');

Notice three deliberate quirks in this data, because they will matter later: Rohan (StudentID 2) has never borrowed a book. Kabir (StudentID 4) has never borrowed a book either. And BorrowID 104 lists StudentID 5 — but there is no student with ID 5 in the Students table at all (perhaps that record was deleted, or a data-entry mistake). Real databases are full of exactly this kind of untidiness, and a good understanding of joins tells you precisely what happens to each of these edge cases.

The column that links the two tables is StudentID. In the Students table it uniquely identifies a row — this is called the primary key. In BorrowedBooks, the same column exists to point back at a Students row — this is called a foreign key. A foreign key is simply "the roll number written in the other register" — it lets one table refer to a row in another without duplicating that row's other details.

The mechanism underneath every join: the Cartesian product

Before learning JOIN syntax, it helps enormously to see what a database engine is conceptually doing when it combines two tables — because every join, no matter how it's written, is built on one basic operation: pairing every row of one table with every row of the other.

In relational database terms, a table's number of columns is called its degree, and its number of rows is called its cardinality. Students has degree 3 (StudentID, Name, Section) and cardinality 4 (four rows). BorrowedBooks also has degree 3 and cardinality 4. If you combine every row of one with every row of the other — with no filtering at all — you get the Cartesian product:

SELECT * FROM Students, BorrowedBooks;

The resulting table's degree is 3 + 3 = 6 (all columns from both tables, side by side), and its cardinality is 4 × 4 = 16 rows. That's because each of the 4 Students rows gets paired once with each of the 4 BorrowedBooks rows: 4 × 4 = 16. Most of these 16 rows are nonsense — for instance, "Rohan" paired with "Panchatantra" even though Rohan never borrowed that book. This is why the Cartesian product is never the final answer you want; it's the raw material a join filters down.

From Cartesian product to a real join: filtering by matching keys

To turn that useless 16-row jumble into a meaningful answer, you keep only the rows where the StudentID in Students actually equals the StudentID in BorrowedBooks. This is called an equi-join, because the filtering condition is an equality test:

SELECT Students.Name, BorrowedBooks.BookTitle
FROM Students, BorrowedBooks
WHERE Students.StudentID = BorrowedBooks.StudentID;

Let's trace this by hand across all 16 combinations mentally: the WHERE clause only lets a pair through when both StudentID values agree. Scanning the 4×4 grid, exactly three pairs satisfy that condition — (Aditi,1)–(101,1), (Aditi,1)–(103,1), and (Meera,3)–(102,3). Every other combination — Rohan with any book, Kabir with any book, anyone with BorrowID 104 — fails the equality test and is discarded. So the output is:

Name    | BookTitle
--------|-------------------
Aditi   | Panchatantra
Aditi   | Malgudi Days
Meera   | Discovery of India

This WHERE-based syntax is the historical form of a join, and CBSE examinations often test it directly under the term "equi-join." Modern SQL offers a cleaner, more explicit way to write the exact same operation — the INNER JOIN:

SELECT Students.Name, BorrowedBooks.BookTitle
FROM Students
INNER JOIN BorrowedBooks
ON Students.StudentID = BorrowedBooks.StudentID;

This produces the identical three-row result. The ON clause plays the same role the WHERE clause played above — it names the matching condition — but writing it as INNER JOIN...ON instead of a plain comma-separated FROM list tells anyone reading the query, immediately, "this is a join, and here is exactly what it matches on," rather than making them guess whether a WHERE clause is filtering or joining. That readability is why virtually every real production query uses JOIN...ON rather than the old comma-and-WHERE style, even though both compute the same result.

Here is the diagram of exactly what just happened — which rows found a partner and which didn't:

A common misconception: "JOIN just glues tables together in row order"

A very natural but wrong mental model is that JOIN works like zipping two lists together — Students' 1st row with BorrowedBooks' 1st row, 2nd with 2nd, and so on. Our own data disproves this cleanly. If matching were purely positional, Students row 2 (Rohan) would get glued to BorrowedBooks row 2 (BorrowID 102, Discovery of India) — but that is factually wrong; Meera borrowed that book, not Rohan. The database never looks at row position. It looks only at the values in the column(s) named in the ON (or WHERE) condition. Reorder either table's rows in storage, insert new rows in the middle, delete rows — the join result stays exactly the same as long as the underlying data is unchanged, because matching is value-based, not position-based. This is worth internalizing early, because it's also why joins remain correct even though real database engines rarely evaluate a literal 4×4 grid — they use smarter techniques (like sorting or hashing on the join column) to find matches quickly, but the logical result is always identical to "compare every pair, keep the equal ones."

A second misconception: thinking INNER JOIN "should" show every student

Students often expect a join between Students and BorrowedBooks to list all four students. It does not — and understanding exactly why is the heart of this topic. INNER JOIN only keeps rows that found a partner on both sides. Rohan and Kabir have zero rows in BorrowedBooks, so there is nothing to pair them with — they vanish from the output entirely, not because of an error, but because "no match" and "not included" are the same thing under INNER JOIN. Symmetrically, BorrowID 104's StudentID of 5 has no partner in Students, so it too vanishes. INNER JOIN, true to its name, keeps only the overlap — the inner region where both tables agree.

When you need every row from one side regardless of a match: LEFT JOIN

Suppose the principal instead asks: "Give me every student, and their borrowed book if they have one — I want to see who's not reading." Now Rohan and Kabir must appear in the answer, even though they have no matching BorrowedBooks row. INNER JOIN cannot do this by definition, because it discards unmatched rows. This is exactly what LEFT JOIN is for: it keeps every row from the left-hand table no matter what, and fills in NULL (SQL's marker for "no value") wherever there's no match on the right.

SELECT Students.Name, BorrowedBooks.BookTitle
FROM Students
LEFT JOIN BorrowedBooks
ON Students.StudentID = BorrowedBooks.StudentID;

Tracing this: the three matched pairs from before appear exactly as before. Then, because Rohan and Kabir are guaranteed a place in the output by LEFT JOIN's rule, two extra rows appear for them with BookTitle set to NULL. BorrowID 104 still does not appear, because it belongs to the right-hand table, and LEFT JOIN's guarantee only protects rows on the left.

Name    | BookTitle
--------|-------------------
Aditi   | Panchatantra
Aditi   | Malgudi Days
Meera   | Discovery of India
Rohan   | NULL
Kabir   | NULL

Five rows in total — three real matches plus two "guaranteed but empty" rows. Note carefully: NULL is not the text "NULL" and it is not zero or an empty string — it is the absence of a value. A query like WHERE BookTitle = NULL will never match anything; testing for it requires WHERE BookTitle IS NULL. This single fact — that ordinary equality comparisons silently fail against NULL — trips up far more programmers than any join syntax does, so it is worth remembering the moment you first meet a LEFT JOIN result containing NULLs.

Naming the remaining family members: RIGHT JOIN and FULL JOIN

Once LEFT JOIN makes sense, the rest of the family follows by symmetry rather than by memorizing new rules. RIGHT JOIN is the mirror image — it keeps every row from the right-hand table and fills NULLs on the left; writing Students RIGHT JOIN BorrowedBooks would guarantee BorrowID 104 appears (with a NULL Name), while Rohan and Kabir would be dropped. FULL JOIN (called FULL OUTER JOIN in some database systems) combines both guarantees at once — every row from both tables appears, with NULLs filled in on whichever side lacks a match; run on our data it would return all five rows from the LEFT JOIN result plus the BorrowID 104 row with a NULL Name — six rows in total. In practice, LEFT JOIN is used far more often than RIGHT JOIN, simply because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping which table is written first in the FROM clause — most SQL style guides prefer that consistency.

Joining more than two tables

Real school databases rarely stop at two tables — you might also have a Subjects table linking to Marks, or a Library table with book details beyond just the title. Joins scale by chaining: each additional JOIN...ON clause brings in one more table, matched on its own key column, and the database processes them in sequence, feeding the result of the first join into the next as if it were itself a single combined table. The logic per step never changes — pair rows, filter by the ON condition, keep or drop based on the join type — only the number of steps grows.

Check your understanding

1. If Students has 6 rows and Marks has 6 rows, what is the cardinality of SELECT * FROM Students, Marks before any WHERE filtering? (Answer: 36, since Cartesian product cardinality is the product of the two cardinalities, 6 × 6.)
2. A LEFT JOIN between Students and BorrowedBooks returns 5 rows; an INNER JOIN between the same two tables returns 3 rows. Explain in one sentence why LEFT JOIN never returns fewer rows than INNER JOIN for the same pair of tables and ON condition. (Answer: LEFT JOIN starts with everything INNER JOIN keeps, and additionally guarantees every left-table row a place, using NULL where INNER JOIN would have dropped that row — so it can only match or exceed INNER JOIN's row count.)
3. Why does BorrowID 104 never appear in either the INNER JOIN or the LEFT JOIN result in this chapter's examples? (Answer: its StudentID value, 5, does not exist in the Students table, so it has no match on the left side under either join type — LEFT JOIN only guarantees rows from the left table, not the right.)
4. Rewrite SELECT * FROM A, B WHERE A.id = B.a_id; using INNER JOIN...ON syntax. (Answer: SELECT * FROM A INNER JOIN B ON A.id = B.a_id; — identical result, clearer intent.)
5. A classmate claims "the WHERE-based join and the INNER JOIN...ON syntax must give different results because they're written so differently." Is this true? (Answer: No — both filter the same Cartesian product using the same equality condition; they are two syntaxes for the identical operation and always return identical results.)

Summary

Data is split across multiple tables to avoid repeating the same facts and risking inconsistency, and a primary key / foreign key pair is how one table points at rows in another. Every join is conceptually built on the Cartesian product — pairing all rows of one table with all rows of another, giving a result with combined degree and multiplied cardinality — followed by keeping only the pairs that satisfy a matching condition, usually equality on a shared column (an equi-join). INNER JOIN...ON is the modern, explicit way to write that filter, and it discards any row on either side that finds no partner. LEFT JOIN changes that guarantee for the left-hand table only, preserving every one of its rows and filling NULL where no match exists — NULL meaning "no value," never to be compared with ordinary equality. RIGHT JOIN mirrors this for the right-hand table, and FULL JOIN offers both guarantees together. Matching is always based on column values, never on row position, and chaining several JOIN...ON clauses is how real multi-table databases answer questions that no single table could answer alone.