SQL Joins - Part 1

Why the Data Is Spread Across Tables

Everything so far has read from a single table. Real databases almost never keep everything in one, and the reason is worth understanding before you learn the syntax for putting them back together.

Imagine storing each order alongside the buyer's name, email and address in one wide table. A student who places twenty orders now has their address written out twenty times. When they move house you have twenty rows to update, you will miss one, and the database is left holding two contradictory addresses with no way to tell which is current. That is called an update anomaly, and avoiding it is most of what database design is about.

The relational answer is to store every fact exactly once. Student details live in students, one row per student. Orders live in orders, one row per order, carrying a student_id column that points at whoever placed it. That pointer is a foreign key: a column whose values are required to exist as a primary key in the other table. The address is now written once and twenty orders refer to it.

This is a one-to-many relationship — one student, many orders — and it is by far the most common shape you will meet. The foreign key always sits on the many side. Putting it on the students row instead would mean each student could hold only one order id, which is exactly backwards, and it is a mistake worth recognising because beginners make it constantly.

A JOIN is the operation that reassembles all of this at read time. It matches rows from two tables using a condition you supply — nearly always "this table's foreign key equals that table's primary key" — and returns the combined columns as if they had been one table all along.

• Primary key — the column that uniquely identifies a row within its own table
• Foreign key — a column holding another table's primary key value, which creates the link
• One-to-many — one student has many orders; the foreign key lives on the orders side
• Many-to-many — students and courses; a third table holds one row per (student, course) pair
• Referential integrity — the database's promise that a foreign key never points at a row that does not exist

-- The parent table: one row per student
CREATE TABLE students (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    email     VARCHAR(150) NOT NULL UNIQUE,
    branch    VARCHAR(20),
    joined_on DATE
);

-- The child table: one row per order, pointing back at a student
CREATE TABLE orders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    student_id   INT,
    total_amount DECIMAL(10,2),
    status       VARCHAR(20),
    ordered_at   DATE,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO students (name, email, branch, joined_on) VALUES
    ('Ananya Sharma', 'ananya@example.com', 'CSE',  '2026-07-01'),
    ('Rahul Verma',   'rahul@example.com',  'ECE',  '2026-07-01'),
    ('Meera Nair',    'meera@example.com',  'CSE',  '2026-07-03'),
    ('Karan Mehta',   'karan@example.com',  'MECH', '2026-07-05');

INSERT INTO orders (student_id, total_amount, status, ordered_at) VALUES
    (1,    1499.00, 'delivered', '2026-07-10'),
    (1,     299.00, 'delivered', '2026-07-18'),
    (2,     899.00, 'shipped',   '2026-07-20'),
    (NULL,  199.00, 'pending',   '2026-07-22');   -- guest order

-- Note: Meera and Karan have no orders, and one order has no student.
-- Those two gaps are what make the join types visibly different.

INNER JOIN: Rows That Match on Both Sides

INNER JOIN pairs each row of the left table with the rows of the right table that satisfy the ON condition, and returns only the pairs that matched. Anything unmatched on either side is dropped without comment.

The ON clause is where the relationship is stated: ON s.id = o.student_id. It is not the same thing as WHERE, and it is not optional — leave it out and most databases hand you a cross join instead, which Part 2 covers and which is almost never what anyone meant.

Aliases such as students s and orders o are conventional in joins, and they do more than save typing. With two tables in play, a bare column name like id is ambiguous and the database refuses it: "Column 'id' in field list is ambiguous". Qualifying every column with its alias removes that error and makes the query far easier to read. Build the habit even when only one of the tables has the column — the day somebody adds a same-named column to the other table, your query keeps working.

Plain JOIN means INNER JOIN; the word INNER is optional in every database. Writing it out anyway is a small kindness to the next reader, because it shows you chose an inner join rather than defaulted into one.

Run the first example against the data above and look carefully at what is missing. Meera and Karan are absent, because they have no orders. The guest order is absent too, because its student_id is NULL and NULL never equals anything — not even another NULL. An inner join discards both silently, which is correct behaviour and still catches people out.

-- Orders, with the name of the student who placed each one
SELECT s.name, o.id AS order_id, o.total_amount, o.ordered_at
FROM students s
INNER JOIN orders o ON s.id = o.student_id
ORDER BY o.ordered_at;

-- INNER is optional; this is the identical query
SELECT s.name, o.total_amount
FROM students s
JOIN orders o ON s.id = o.student_id;

-- Filtering a joined result: WHERE runs after the join is built
SELECT s.name, s.branch, o.total_amount
FROM students s
JOIN orders o ON s.id = o.student_id
WHERE o.total_amount > 500
  AND s.branch = 'CSE';

-- Aggregating a joined result
SELECT s.branch, COUNT(*) AS orders, SUM(o.total_amount) AS revenue
FROM students s
JOIN orders o ON s.id = o.student_id
GROUP BY s.branch;

-- Ambiguous, and rejected: both tables have a column called id
-- SELECT id, name FROM students s JOIN orders o ON s.id = o.student_id;

A Join Can Multiply Your Rows

Here is the join behaviour that surprises people most, and it is not an edge case — it happens on completely ordinary data. A join does not return "one row per student". It returns one row per matching pair. Ananya has two orders, so joining students to orders produces two rows for Ananya, with her name, email and branch repeated on both.

That is correct and necessary, because you asked for order-level detail. It also wrecks any aggregate computed over the wrong column. COUNT(*) on that result counts order rows, not students. Worse, join orders to order_items and then sum orders.total_amount, and each order's total gets added once for every item it contains. Revenue comes out inflated by a factor that depends on basket size — and the number still looks plausible, which is precisely what makes this dangerous.

The defences are straightforward once you know to look for the problem. Count the thing you actually mean: COUNT(DISTINCT s.id) for students, COUNT(*) for order rows. Sum at the level where the values genuinely live: add up oi.quantity * oi.unit_price from the items themselves, or aggregate the items in a subquery first so the join receives exactly one row per order.

A sanity check that costs nothing: run SELECT COUNT(*) on the base table, then on the joined query. If the second number is larger, your join has multiplied rows and every aggregate after it needs a second look. If it is smaller, an inner join has quietly dropped rows that you may have wanted to keep.

-- Ananya appears twice, once per order
SELECT s.id, s.name, o.id AS order_id, o.total_amount
FROM students s
JOIN orders o ON s.id = o.student_id
ORDER BY s.id;

-- How many students placed an order? Not COUNT(*).
SELECT COUNT(*) AS order_rows
FROM students s JOIN orders o ON s.id = o.student_id;

SELECT COUNT(DISTINCT s.id) AS students_who_ordered
FROM students s JOIN orders o ON s.id = o.student_id;

-- INFLATED: total_amount is repeated once per item in the order
-- SELECT SUM(o.total_amount) AS revenue
-- FROM orders o JOIN order_items oi ON oi.order_id = o.id;

-- Correct: sum at the level where the values actually live
SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi;

-- Or aggregate first, so the join sees one row per order
SELECT o.id, o.ordered_at, t.item_total
FROM orders o
JOIN (
    SELECT order_id, SUM(quantity * unit_price) AS item_total
    FROM order_items
    GROUP BY order_id
) t ON t.order_id = o.id;

LEFT JOIN: Keep Everything on the Left

An INNER JOIN answers "which students placed orders". A LEFT JOIN answers "how many orders did each student place, including the ones who placed none". Those are different questions, and the second comes up constantly — every "list all X with their Y" screen is really asking it.

LEFT JOIN keeps every row of the left table whether or not it found a match. Where there was no match, the right table's columns come back as NULL. Meera and Karan therefore appear in the result with a NULL order id, a NULL amount and a NULL date. The full name is LEFT OUTER JOIN and the word OUTER is optional everywhere.

"Left" means the table named before the JOIN keyword, so the order you write the tables in now carries meaning. That is a good reason to put the table you care about first and to keep every join in a query pointing the same way. A chain of LEFT JOINs reads cleanly from top to bottom; a query mixing left and right joins is genuinely hard to reason about, which is most of why RIGHT JOIN is so rare in real code.

Now the classic mistake. Count orders per student with a LEFT JOIN and COUNT(*), and the students with no orders come back with a count of 1 rather than 0. The padded row physically exists, so COUNT(*) dutifully counts it. Use COUNT(o.id) instead — that counts non-NULL order ids, so a student whose only row is padding scores zero. The same care applies to sums: COALESCE(SUM(o.total_amount), 0) turns a student's NULL total into a clean 0.

-- Every student, orders or not
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
ORDER BY s.name;
-- Meera and Karan appear, with NULLs in the order columns

-- WRONG: students with no orders come back with a count of 1
SELECT s.name, COUNT(*) AS orders
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
GROUP BY s.id, s.name;

-- RIGHT: count a column from the right table, so padding scores 0
SELECT
    s.name,
    COUNT(o.id)                      AS orders,
    COALESCE(SUM(o.total_amount), 0) AS spent
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
GROUP BY s.id, s.name
ORDER BY spent DESC;

The Bug That Turns a LEFT JOIN Back Into an INNER JOIN

This is the most common join bug in production code, and it raises no error at all. It just quietly returns fewer rows than it should.

Write a LEFT JOIN from students to orders, then add WHERE o.status = 'delivered'. Meera and Karan vanish from the result. The LEFT JOIN did its job and gave them a row padded with NULLs — and then WHERE ran, NULL = 'delivered' was not true, and those padded rows were filtered straight out again. The outer join has been converted back into an inner join by a condition placed one line too low.

The rule to remember is that a condition about the right table of a LEFT JOIN belongs in the ON clause. ON decides which rows count as a match, and unmatched left rows are still kept and padded afterwards. WHERE decides which rows of the finished result survive, and it judges the padded rows by the same standard as everything else.

Conditions about the left table are the opposite case. WHERE s.branch = 'CSE' belongs exactly where it is, because you genuinely do want those students gone from the output rather than kept with empty order columns.

There is one legitimate exception that looks like the bug and is not: WHERE o.id IS NULL. That condition deliberately tests for the padded rows, and it is the whole basis of the anti-join pattern in the next section. It is the only right-table condition that properly belongs in WHERE after a LEFT JOIN.

-- LOOKS like a LEFT JOIN, behaves like an INNER JOIN.
-- Students with no delivered order disappear entirely.
SELECT s.name, o.id AS order_id
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
WHERE o.status = 'delivered';

-- Fixed: the condition on the right table moves into ON
SELECT s.name, o.id AS order_id
FROM students s
LEFT JOIN orders o
       ON s.id = o.student_id
      AND o.status = 'delivered'
ORDER BY s.name;
-- every student is listed; those with no delivered order show NULLs

-- A condition on the LEFT table still belongs in WHERE
SELECT s.name, COUNT(o.id) AS delivered_orders
FROM students s
LEFT JOIN orders o
       ON s.id = o.student_id
      AND o.status = 'delivered'
WHERE s.branch = 'CSE'
GROUP BY s.id, s.name;

Finding the Rows That Have No Match

"Which students have never placed an order?" turns out to be a very common shape of question — dormant users, products never sold, invoices never paid — and the join answer is neat. Do the LEFT JOIN, then keep only the rows where the right side came back NULL. Those are precisely the left rows that found no partner. The pattern is called an anti-join.

Test a column that can never legitimately be NULL in a matched row; the right table's primary key is the safe choice. Testing something nullable such as o.status would also catch genuinely matched orders whose status happened to be blank, which is a quiet way to produce a wrong answer that nobody questions.

Two other spellings do the same job. NOT EXISTS is usually the clearest, reads closest to the English question, and is what most experienced developers reach for. NOT IN looks equally natural and carries the NULL trap from the deleting lesson: if any row of the subquery has NULL in the compared column — and our guest order does — NOT IN returns nothing whatsoever. Not fewer rows. None.

So the practical guidance is short. Use NOT EXISTS by default. Use LEFT JOIN ... IS NULL when you also want columns out of the join. Use NOT IN only when you are certain the subquery cannot produce a NULL, which usually means adding WHERE the_column IS NOT NULL yourself. On a modern optimiser the first two normally produce the same execution plan anyway, so pick the one that reads better.

-- Anti-join: students who have never ordered
SELECT s.id, s.name, s.email
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
WHERE o.id IS NULL;

-- The same question with NOT EXISTS, which reads more directly
SELECT s.id, s.name, s.email
FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.student_id = s.id
);

-- The NOT IN version returns NOTHING on this data, because one
-- order row has student_id = NULL
-- SELECT s.id, s.name FROM students s
-- WHERE s.id NOT IN (SELECT student_id FROM orders);

-- Making NOT IN safe again by removing the NULLs yourself
SELECT s.id, s.name
FROM students s
WHERE s.id NOT IN (
    SELECT student_id FROM orders WHERE student_id IS NOT NULL
);

-- The other direction: orders that belong to no student
SELECT o.id, o.total_amount
FROM orders o
LEFT JOIN students s ON s.id = o.student_id
WHERE s.id IS NULL;