SQL JOINs Explained with Examples

JOIN is the most important operation in SQL after SELECT. It is also the most misunderstood. Developers often guess between INNER and LEFT JOIN, hope for the best, and move on. When the row count is wrong or rows are mysteriously missing, they add DISTINCT or GROUP BY as a band-aid.

This post explains every major JOIN type with real data, clear rules, and the exact SQL you need. By the end, you will know which JOIN to reach for and why.

Our sample data

Every example in this post uses the same two tables so you can compare results directly.

employees

departments

David has no department. Eve references department 50, which does not exist. Sales exists but has no employees. These edge cases are where JOINs get interesting.

INNER JOIN — only matching rows

INNER JOIN returns rows where the join condition matches in both tables. If either side is missing, the row is dropped.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

David is excluded because his dept_id is NULL. Eve is excluded because department 50 does not exist. Sales is excluded because it has no matching employees. INNER JOIN is strict: no match, no row.

Use INNER JOIN when you only care about rows that exist in both tables.

LEFT JOIN — all left rows, matched right data

LEFT JOIN returns every row from the left table, plus matching data from the right. If there is no match, right-side columns become NULL.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Now David and Eve appear because every employee is kept. Their department is NULL because there was no match. Sales still does not appear because it is on the right side and has no matching left row.

Use LEFT JOIN when you need all rows from the primary table, even if related data is missing.

RIGHT JOIN — all right rows, matched left data

RIGHT JOIN is the mirror of LEFT JOIN. It returns every row from the right table, plus matching data from the left.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Sales appears because every department is kept. Alice, Bob, and Carol appear because they match. David and Eve are dropped because they are on the left and have no matching right row.

RIGHT JOIN is rare in practice. Most developers flip the table order and use LEFT JOIN instead, which is easier to reason about.

FULL OUTER JOIN — everything from both sides

FULL OUTER JOIN returns all rows from both tables. Where there is a match, columns are populated. Where there is no match, the missing side is NULL.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

This is the union of LEFT and RIGHT JOIN. Every employee appears. Every department appears. It is the most inclusive JOIN type.

Use FULL OUTER JOIN when you need a complete view of both datasets, including unmatched rows on either side.

CROSS JOIN — every combination

CROSS JOIN produces the Cartesian product: every row from the left table paired with every row from the right.

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

With 5 employees and 3 departments, this returns 15 rows. Each employee appears three times, once per department. CROSS JOIN has no ON clause because it matches everything.

Use CROSS JOIN sparingly. It is useful for generating test data or when you genuinely need every combination.

SELF JOIN — a table joined to itself

A SELF JOIN is not a separate keyword. It is any JOIN where a table is joined to itself using aliases.

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

This pattern is common for hierarchical data: managers who are also employees, categories with parent categories, or threaded comments.

Common mistakes and how to avoid them

• Missing rows after INNER JOIN. If your count drops unexpectedly, switch to LEFT JOIN and look for NULLs. They reveal where your data is incomplete.
• Duplicate rows from JOINs. If the right table has multiple matches for one left row, the left row repeats. Use COUNT or DISTINCT if this is unexpected.
• Filtering in the WHERE clause instead of ON. LEFT JOIN ... WHERE d.id = 10 turns your LEFT JOIN into an INNER JOIN because NULLs fail the filter. Move the condition to the ON clause or use IS NULL explicitly.
• Assuming NULLs match. NULL never equals NULL in SQL. A row with NULL in the join column will never match anything, even another NULL.

Performance tips

• Index join columns. The columns in your ON clause should be indexed. Without an index, the database may scan the entire right table for every left row.
• Join order matters. The query optimizer usually handles this, but on complex queries with many tables, the order you write JOINs can affect the execution plan.
• Avoid CROSS JOINs on large tables. Two tables with 10,000 rows each produce 100 million rows. This will hang most databases.
• Use EXPLAIN. Before optimizing, run EXPLAIN on your query to see if the database is using indexes or falling back to sequential scans.

From JOINs to schema changes

JOINs help you query across tables. But what happens when the tables themselves change — columns are renamed, foreign keys are added, or tables are split? That is where SchemaLens helps.

Paste your old schema into Schema A, paste your updated schema into Schema B, and see every change instantly. SchemaLens highlights new columns, dropped tables, and type changes that could break your JOINs.

Related reading: SQL Data Types Across Dialects · How to Design a Schema That Scales · Complete Guide to Database Indexing