SQL Joins Explained: INNER, LEFT, RIGHT, FULL & SELF

Quick question. Flipkart stores your name and address. It also stores your orders. Should every order row repeat your full name and address? Think about what happens when you shift to a new flat — someone would have to update your address on 50 old order rows. Miss one, and your refund reaches your old landlord. So databases store things once — and stitch them back together with joins, the most important (and most-tested) idea in all of SQL.

Why data lives in separate tables

Store every fact once, in its own table, and connect tables with IDs. Our running example is a small shop — customers in one table, orders in another:

Each order carries just a small customer_id pointing back at its owner — that pointer is called a foreign key. Aditi (id 1) has two orders; Karan (id 4) has none. Notice we never wrote Aditi's name inside the orders table. Clean. But now every useful question — "show each order with the customer's name" — needs data from both tables at once. Enter the join.

INNER JOIN — only the rows that match

SELECT c.customer_name, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Aditi Verma | 101 | 1200.00
Aditi Verma | 102 |  800.00
Rohan Gupta | 103 |  500.00
Sneha Iyer  | 104 | 1200.00

Read the ON clause as the matching rule: pair up rows whose IDs agree. The little letters c and o are table aliases — nicknames so you don't type full table names everywhere. Also worth knowing: plain JOIN means INNER JOIN; the word INNER is optional.

LEFT and RIGHT JOIN — keep one whole side

Now the marketing team asks: "list all customers, with their orders if any". Dropping Karan is now wrong — he's exactly who marketing wants to find! LEFT JOIN keeps every row of the left (first-written) table, and where no match exists, fills the right side with NULL:

SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Aditi Verma | 101
Aditi Verma | 102
Rohan Gupta | 103
Sneha Iyer  | 104
Karan Mehta | NULL   ← kept, no match

The anti-join — finding who's missing

Here's the beautiful trick hiding inside that LEFT JOIN. The NULLs aren't junk — they're a signal: they mark exactly the customers who never ordered. Filter on them and you get the famous anti-join pattern:

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Karan Mehta

LEFT JOIN + IS NULL = "find the ones with no match". One pattern, endless exam variants: customers who never ordered, products never sold, students who never attempted a test, employees with no manager. Learn it once, answer it forever.

FULL OUTER JOIN — and MySQL's missing piece

FULL OUTER JOIN keeps both sides — matched rows once, plus unmatched rows from each table with NULLs on the other side. The catch every interviewer knows: MySQL doesn't support it natively. The standard workaround is to union a LEFT and a RIGHT join:

SELECT c.customer_name, o.order_id
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_name, o.order_id
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

The follow-up they always ask: why UNION and not UNION ALL? Because rows that matched on both sides appear in both halves — UNION de-duplicates them, UNION ALL would show every matched row twice.

SELF JOIN — a table looking into a mirror

A puzzle. The employees table has a manager_id column — and the manager is also an employee in the same table. To print "employee → manager name", what second table do you join to? The same one. Open it twice with two aliases:

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Alice Sharma | NULL
Bob Iyer     | Alice Sharma
Esha Nair    | Alice Sharma

Think of e and m as two photocopies of the same register lying side by side. Self joins feel strange exactly once — then they're obvious forever. Any table that stores a relationship between its own rows (employee→manager, category→parent category, friend→friend) is self-join territory.

CROSS JOIN — every combination, on purpose (or by accident)

A t-shirt shop with 3 sizes and 4 colours has 3 × 4 = 12 variants. CROSS JOIN builds exactly that — the Cartesian product, every row of A paired with every row of B, no ON clause at all:

SELECT s.size, c.colour
FROM sizes s
CROSS JOIN colours c;

Useful when you genuinely want combinations. Dangerous when you forget your ON condition — in MySQL, SELECT * FROM a JOIN b with no ON quietly becomes a cross join, and two lakh-row tables silently produce crores of pairs. If a query suddenly returns way too many rows, a missing join condition is suspect number one.

Wait — the trap that inflates your numbers

This one separates people who've used joins from people who've memorised them. Order 101 is worth ₹1200 and contains 2 items. Join orders to order_items, and order 101 now occupies 2 result rows — so SUM(o.total_amount) counts the ₹1200 twice:

-- WRONG: order 101's amount is summed once per item
SELECT SUM(o.total_amount)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id;

-- RIGHT: aggregate the orders table alone (or sum item-level columns)
SELECT SUM(total_amount) FROM orders;

This is the join fan-out problem: joining one-to-many duplicates the "one" side once per matching "many" row. Your revenue report becomes silently wrong, and nobody notices until finance does. Aggregate first, join later — or sum columns that belong to the "many" side.

Common mistakes

• Using INNER JOIN when the question says "all customers, including those without…" — that phrasing is begging for a LEFT JOIN.
• Forgetting the ON condition and getting an accidental cross join.
• Summing a "one"-side column after joining one-to-many (the fan-out trap above).
• Filtering the right table in WHERE after a LEFT JOIN — WHERE o.status = 'paid' throws away the NULL rows and silently turns your LEFT JOIN back into an INNER JOIN. Put such conditions in the ON clause instead.
• Writing a self join with one alias — you need two names for the two copies.

Quick recap

Practice Zone — PYQs from real selection rounds

A proper e-commerce schema — customers, orders, order_items, products — just like interviewers draw on the whiteboard. Attempt first, reveal second.

Q1INNER JOINeasy

customers INNER JOIN orders ON customers.customer_id = orders.customer_id — which rows land in the result?

Asked in

AEvery customer, whether or not they have ordersBOnly rows where a customer and an order actually matchCEvery order, even ones with no valid customerDEvery possible customer–order pair

Q2LEFT vs RIGHT JOINeasy

In customers LEFT JOIN orders, which side is guaranteed to appear fully in the result?

Asked in

AEvery customer — order columns become NULL where there's no matchBEvery order — customer columns become NULL where there's no matchCBoth tables appear fullyDOnly the matching rows appear

Q3SELF JOINmedium

One employees table stores each employee's manager_id, which points at another employee in the same table. To list each employee with their manager's name, I need a…

Asked in

ASELF JOIN — join the table to itself using two aliasesBCROSS JOIN — pair every employee with every otherCFULL OUTER JOIN between employees and managersDUNION of the employee and manager rows

Q4CROSS JOINmedium

Table sizes has 3 rows and table colors has 4 rows. How many rows does SELECT * FROM sizes CROSS JOIN colors; return?

Asked in

A7B12C4D3

Q5Join fan-out pitfallhard

Order 101 has 2 rows in order_items. A developer joins orders to order_items and then computes SUM(orders.total_amount). What happens to order 101's amount?

Asked in

AIt's counted once — SUM is smart about joinsBIt's counted twice, inflating the totalCIt becomes NULL because of the joinDThe query fails with a grouping error

Q6FULL OUTER JOIN in MySQLhard

MySQL has no native FULL OUTER JOIN. How do you emulate one between tables a and b?

Asked in

Aa LEFT JOIN b … UNION … a RIGHT JOIN bBa CROSS JOIN b with a WHERE filterCa INNER JOIN b plus ORDER BY on both keysDIt's impossible in MySQL

FAQ

Next lesson: squeezing thousands of rows into one answer per group — Lesson 3: GROUP BY & HAVING →