SQL JOINs Complete Guide: INNER, LEFT, RIGHT, FULL, and CROSS Explained

JOINs are the most tested concept in SQL interviews — and the most misunderstood.

Most candidates can write a basic JOIN. Fewer can explain why they chose it, handle edge cases correctly, or debug when a join inflates row counts unexpectedly.

This guide walks you through every type of JOIN with real examples, interview framing, and the common pitfalls that trip people up.

1. How JOINs actually work

A JOIN combines rows from two tables based on a matching condition — usually a shared key.

Before writing any JOIN, ask yourself two questions:

1. What rows do I want from the left table? (all of them, or only matching ones?)
2. What rows do I want from the right table? (all of them, or only matching ones?)

Your answer determines which JOIN to use.

2. INNER JOIN — only matching rows

"Return customers who have placed at least one order."

sql
SELECT
  c.customer_id,
  c.name,
  o.order_id,
  o.amount
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

What it does: returns only rows where customer_id exists in both tables.

Customers with no orders disappear from the result.

Interview framing: "INNER JOIN is an intersection — I only get rows where the join condition matches on both sides."

Common mistake: forgetting that INNER JOIN silently drops non-matching rows. If your result set is smaller than expected, check your join type first.

3. LEFT JOIN — all left rows, matched right rows

"Show all customers and how many orders they have placed, including customers with zero orders."

sql
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

What it does: keeps every row from the left table (customers), and fills right-table columns with NULL when there is no match.

Why COUNT(o.order_id) and not COUNT(*)?

• COUNT(*) counts the NULL row as 1 — wrong for customers with no orders.
• COUNT(o.order_id) counts only non-NULL values — correctly returns 0.

Pattern to remember: Whenever the question says "including users who never..." or "even if there are no...", reach for LEFT JOIN.

4. RIGHT JOIN — all right rows, matched left rows

sql
SELECT
  c.customer_id,
  c.name,
  o.order_id
FROM customers c
RIGHT JOIN orders o
  ON c.customer_id = o.customer_id;

What it does: keeps every row from the right table (orders), even if no matching customer exists.

In practice, RIGHT JOIN is rare. Almost every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order — which is usually clearer.

Interview tip: If an interviewer asks about RIGHT JOIN, mention that you prefer rewriting as LEFT JOIN for readability. It shows you think about code clarity, not just correctness.

5. FULL OUTER JOIN — all rows from both sides

sql
SELECT
  c.customer_id,
  c.name,
  o.order_id,
  o.amount
FROM customers c
FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id;

What it does: returns all rows from both tables. Where there is no match, the missing side fills with NULL.

Use cases:

• Data reconciliation: find records in table A not in B, and vice versa.
• Audit reports: see everything regardless of whether it matches.

Note: MySQL does not support FULL OUTER JOIN natively. You can simulate it with LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left.id IS NULL.

6. CROSS JOIN — every combination

sql
SELECT
  p.product_name,
  s.size_name
FROM products p
CROSS JOIN sizes s;

What it does: produces the Cartesian product — every row in the left table paired with every row in the right table.

If products has 10 rows and sizes has 5 rows, the result has 50 rows.

When to use it:

• Generating all combinations (e.g., all products × all regions for a reporting template)
• Building a date spine (all dates × all users)

Warning: accidental CROSS JOINs on large tables can return millions of rows and crash queries. Always double-check your join condition.

7. SELF JOIN — a table joined to itself

"Find all pairs of employees who share the same manager."

sql
SELECT
  a.employee_id AS emp1,
  b.employee_id AS emp2,
  a.manager_id
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
  AND a.employee_id < b.employee_id;

What it does: joins the employees table to itself using aliases to distinguish the two copies.

The a.employee_id < b.employee_id condition prevents duplicates (e.g., pairing emp 1 with emp 2 and also emp 2 with emp 1).

Self joins appear in hierarchy, pair-finding, and gap-detection problems.

8. Anti-join — find rows with NO match

"Find customers who have never placed an order."

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

This is the anti-join pattern: LEFT JOIN + filter on NULL in the right table.

You can also write it with NOT EXISTS:

sql
SELECT customer_id, name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Both approaches work. The NOT EXISTS version can be more readable for complex conditions.

9. The row-count inflation trap

This is the single most common JOIN mistake in interviews and production.

sql
-- orders table has multiple rows per customer
-- customers has one row per customer

SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

If a customer placed 5 orders, their name appears 5 times. This is correct behavior — but many candidates are surprised by it.

When you then aggregate:

sql
SELECT c.customer_id, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

This is fine. But if you join two one-to-many tables on the same fact table, row counts can explode.

Interview tip: Always state the cardinality of the relationship before writing a JOIN: "customers is one-to-many with orders, so I expect the join to produce multiple rows per customer."

10. How to pick the right JOIN in an interview

When you read a JOIN question, walk through this decision tree:

1. Do I need rows from the left table even with no match? → LEFT JOIN
2. Do I need rows from both tables even with no match? → FULL OUTER JOIN
3. Do I want only rows that match on both sides? → INNER JOIN
4. Do I want every combination? → CROSS JOIN
5. Do I want rows from the left with no match at all? → Anti-join (LEFT JOIN + IS NULL)

Being explicit about this reasoning in an interview — even briefly — signals strong SQL fundamentals.

11. Where to practice JOIN problems

The best way to get comfortable with JOINs is to practice on realistic multi-table schemas where the join choice actually matters.

On TheQueryLab, you can work through problems that involve:

• Two-table joins with aggregation
• Multi-table joins across 3–4 tables
• Anti-join patterns
• Self-join hierarchy problems

Start with the SQL Interview Preparation Kit — the problems are ordered by pattern, so you build on each concept progressively rather than jumping between random puzzles.