All Articles

SQL JOINs Explained: INNER, LEFT, RIGHT, FULL, and CROSS JOIN with Examples

Fundamentals
#sql-joins#inner-join#left-join#database#sql-basics

Understanding SQL JOINs

JOINs are the foundation of relational database queries. They let you combine rows from two or more tables based on a related column, which is exactly what relational databases are designed to do — store data once, in normalized tables, and reassemble it on demand. Mastering JOINs is essential for any SQL interview and for real-world data work, since almost no useful query touches only one table.

This guide walks through every JOIN type — INNER, LEFT, RIGHT, FULL OUTER, CROSS, and SELF — with sample data, full result sets, performance notes, and the interview questions that come up most often around them.

The Sample Data

We'll use two simple tables throughout this guide:

-- employees table
| id | name      | department_id |
|----|-----------|---------------|
| 1  | Alice     | 1             |
| 2  | Bob       | 2             |
| 3  | Charlie   | 1             |
| 4  | Diana     | NULL          |

-- departments table
| id | name           |
|----|----------------|
| 1  | Engineering    |
| 2  | Marketing      |
| 3  | Sales          |

Notice two intentional edge cases in this data: Diana has no department (department_id is NULL), and Sales has no employees assigned to it. Keep an eye on how each JOIN type handles these two rows — that's where the real differences show up.

1. INNER JOIN

Returns only rows where there's a match in both tables. This is the most common JOIN type and, unless you specifically need unmatched rows, usually the right default.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- Result:
-- | name    | department    |
-- |---------|---------------|
-- | Alice   | Engineering   |
-- | Bob     | Marketing     |
-- | Charlie | Engineering   |

Notice Diana is excluded because she has no department, and Sales is excluded because no employees belong to it. Both unmatched rows simply disappear — this is the defining behavior of an INNER JOIN.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, with matching data from the right table where it exists. If there's no match, the right side's columns show NULL. Use LEFT JOIN whenever the left table is your "source of truth" and you need every row from it regardless of whether related data exists.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Result:
-- | name    | department    |
-- |---------|---------------|
-- | Alice   | Engineering   |
-- | Bob     | Marketing     |
-- | Charlie | Engineering   |
-- | Diana   | NULL          |

This is the JOIN type behind one of the most common real-world queries: "find all X that have no matching Y" (an anti-join), by adding WHERE d.id IS NULL:

-- Find employees with no assigned department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

3. RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image of LEFT JOIN — returns all rows from the right table, with matching data from the left where it exists.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- Result:
-- | name    | department    |
-- |---------|---------------|
-- | Alice   | Engineering   |
-- | Bob     | Marketing     |
-- | Charlie | Engineering   |
-- | NULL    | Sales         |

RIGHT JOIN is functionally identical to swapping the table order and using LEFT JOIN — most style guides (and most engineers) prefer to always write LEFT JOIN and reorder the tables instead, purely for readability consistency across a codebase.

4. FULL OUTER JOIN

Returns all rows from both tables, matched where possible, with NULLs filled in on whichever side has no match. Useful for reconciliation tasks — comparing two datasets to find everything that doesn't line up on either side.

SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- Result:
-- | name    | department    |
-- |---------|---------------|
-- | Alice   | Engineering   |
-- | Bob     | Marketing     |
-- | Charlie | Engineering   |
-- | Diana   | NULL          |
-- | NULL    | Sales         |

Database compatibility note: MySQL does not support FULL OUTER JOIN directly. You have to emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT e.name, d.name AS department
FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

5. CROSS JOIN

Returns the Cartesian product — every row from the first table paired with every row from the second table, with no join condition at all. Row count is the product of both table sizes, so use it with caution on anything but small, intentional datasets.

SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;

-- Result: 4 employees × 3 departments = 12 rows
-- | name    | department    |
-- |---------|---------------|
-- | Alice   | Engineering   |
-- | Alice   | Marketing     |
-- | Alice   | Sales         |
-- | Bob     | Engineering   |
-- | ...     | ...           |

CROSS JOIN has legitimate uses: generating combinations (every product × every size/color variant), building a date-spine to fill in missing calendar days, or creating test/seed data. Accidental CROSS JOINs — usually from forgetting a JOIN condition — are one of the most common causes of runaway query costs in production.

6. Self JOIN

A self join is when a table is joined with itself, treated as if it were two separate tables via aliases. This is the standard pattern for hierarchical or relational data within a single table, like employee-manager relationships or a "follows" table in a social app.

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

LEFT JOIN is typical here — top-level employees (the CEO, for example) have no manager, and you usually still want them in the result set with NULL in the manager column.

7. Semi-Joins and Anti-Joins (EXISTS / NOT EXISTS)

Not every "combine two tables" question needs a true JOIN. If you only need to check whether a related row exists — without pulling any columns from the other table — EXISTS and NOT EXISTS are usually clearer and often faster than a JOIN with DISTINCT.

-- Semi-join: customers who have placed at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Anti-join: customers who have never placed an order
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

A JOIN would return one row per matching order, which means you'd need an extra DISTINCT or GROUP BY to get one row per customer — EXISTS avoids that duplication problem entirely, since it only checks for existence and doesn't multiply rows.

JOIN Performance Notes

  • Always join on indexed columns. An unindexed join column forces a sequential scan on that side of the join, which gets dramatically worse as tables grow.
  • Match data types on both sides of the join condition. Joining an INT column to a VARCHAR column forces an implicit cast that silently disables the index.
  • Prefer INNER JOIN over LEFT JOIN when you don't actually need unmatched rows — outer joins carry extra planning overhead and can prevent certain optimizations like early filtering.
  • Watch for join fan-out. Joining a table to another table where the join key isn't unique multiplies rows — this is a common, hard-to-spot source of double-counted totals in analytics queries (e.g. joining orders to order_items before summing revenue).
  • The database engine, not you, usually decides how to physically execute the join — nested loop, hash join, or merge join — based on table sizes and available indexes. Read the execution plan (EXPLAIN) if a JOIN is unexpectedly slow.

Common JOIN Mistakes

  • Confusing ON and WHERE with outer joins. Putting a filter on the right-hand table in WHERE instead of ON silently turns a LEFT JOIN back into an INNER JOIN, because WHERE runs after the join and drops any row where the filtered column is NULL.
  • Forgetting the join condition, which produces an accidental CROSS JOIN and a row-count explosion.
  • Not accounting for NULLs in the join column — NULL = NULL is never true in SQL, so rows with NULL foreign keys never match in a standard join.
  • Double-counting after a one-to-many join when aggregating — always check whether your join key is actually one-to-one before running SUM() or COUNT() across joined tables.
-- Mistake: this silently behaves like an INNER JOIN, not a LEFT JOIN
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';   -- drops Diana, since NULL != 'Engineering'

-- Fix: move the condition into ON if you want to keep unmatched left rows
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Engineering';

Common JOIN Interview Questions

  1. What's the difference between INNER and LEFT JOIN? INNER JOIN returns only matching rows from both tables; LEFT JOIN returns all rows from the left table plus matches from the right, filling unmatched columns with NULL.
  2. When would you use a FULL OUTER JOIN? When you need to see all records from both tables regardless of match — classic use case is reconciling two lists to find records that exist on only one side.
  3. Can you JOIN more than two tables? Yes — chain multiple JOINs: FROM a JOIN b ON ... JOIN c ON .... The optimizer decides the actual execution order internally.
  4. What's the difference between ON and WHERE? ON defines the join condition and is applied while rows are being matched; WHERE filters the already-joined result set — which matters a great deal for outer joins, as shown above.
  5. What is a Cartesian product and how do you avoid it accidentally? It's the result of a JOIN with no matching condition (or a CROSS JOIN) — every row of one table paired with every row of the other. Avoid it by always double-checking your ON clause is present and correct.
  6. How would you find rows in Table A that have no match in Table B? LEFT JOIN Table B and filter WHERE b.id IS NULL, or use WHERE NOT EXISTS (...) — the anti-join pattern.
  7. Why might a LEFT JOIN return more rows than the left table has? If the join key on the right table isn't unique, each left row can match multiple right rows, multiplying the result — this is join fan-out.

Frequently Asked Questions

Is there a performance difference between JOIN and WHERE-based (implicit) joins?

Functionally they can produce the same result for inner joins, but explicit JOIN ... ON syntax is the modern standard: it's clearer, less error-prone, and is the only syntax that supports outer joins cleanly. Nearly every style guide recommends explicit JOIN syntax exclusively.

Does the order of tables in a JOIN affect the result?

For INNER JOIN and FULL OUTER JOIN, no — the result is the same either way. For LEFT/RIGHT JOIN, yes — swapping the tables changes which side's unmatched rows are preserved.

Can you JOIN on more than one column?

Yes — combine conditions with AND inside the ON clause, e.g. ON a.id = b.a_id AND a.region = b.region. This is common with composite keys.

Practice Makes Perfect

The best way to master JOINs is to practice against real, imperfect data — where NULLs, duplicate keys, and mismatched types actually show up. Try our SQL practice questions with live database environments, work through case studies that require complex multi-table queries, or continue to our companion guide on SQL query optimization to learn how to keep those JOINs fast at scale.

Back to All Articles