SQL JOINs Explained: INNER, LEFT, RIGHT & FULL

Real data lives in more than one table. A JOIN combines rows from two tables based on a related column between them — for example, matching each Employees.dept_id to a Departments.dept_id. We'll use these two small tables throughout:

Notice: Priya's department (30) doesn't exist in Departments, Wei's department (99) doesn't exist either, and Support (40) has no employees yet. These mismatches are exactly what make the different join types behave differently.

INNER JOIN — only the matches

INNER JOIN returns rows only when the join condition matches on both sides. Anything that doesn't match is dropped.

SELECT e.first_name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON d.dept_id = e.dept_id;

On the two small tables above, the result is Ananya/Engineering and Tomas/Sales: Priya and Wei are dropped (no matching department), and Support doesn't appear (no matching employee). The rows shown under each query come from the site's full sample database, where every employee has a real department — so there INNER JOIN keeps all 12 employees, and only Support goes missing.

LEFT JOIN — everything from the left table

LEFT JOIN keeps every row from the "left" table (the one named right after FROM), and fills in NULL for the right-hand columns when there's no match.

SELECT e.first_name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON d.dept_id = e.dept_id;

On the small tables: all four employees appear, and Priya and Wei get NULL for dept_name. On the full sample database every employee has a match, so LEFT JOIN returns the same 12 rows as INNER JOIN — which is the point: a LEFT JOIN only differs when something on the left has no partner.

This is the join to reach for when you're asking "show me every X, and whatever related Y exists (if any)" — for example, every product alongside its total orders, including products that have never been ordered.

RIGHT JOIN — everything from the right table

The mirror image of LEFT JOIN: every row from the right-hand table is kept, with NULLs on the left where there's no match.

SELECT e.first_name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON d.dept_id = e.dept_id;

On the small tables: Ananya/Engineering, Tomas/Sales, and NULL/Support — Support has no employees, but the department still appears. The full sample database behaves the same way: 13 rows, one of them Support with a NULL first_name.

In practice, most people just swap the table order and use LEFT JOIN instead — the results are identical, and it reads more naturally for most queries.

FULL OUTER JOIN — everything, matched where possible

FULL JOIN keeps every row from both tables, matching where it can and filling NULLs on whichever side has no counterpart.

SELECT e.first_name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON d.dept_id = e.dept_id;

On the small tables: all four employees and all three departments, including Priya/NULL, Wei/NULL, and NULL/Support. On the full sample database: 13 rows — the 12 matched employees plus Support with nobody in it.

The word OUTER is optional, so FULL JOIN means the same thing, and SQLite, which runs the playground, accepts both spellings. MySQL is the exception worth knowing: it has no FULL OUTER JOIN at all, so there the same result is built by combining a LEFT JOIN and a RIGHT JOIN with UNION.

SELF JOIN — joining a table to itself

Nothing says the two sides of a join have to be different tables. When a table holds a reference to its own rows — an employee's manager_id pointing at another employee's emp_id — you join the table to itself and give each side a different alias. Those aliases are what make it work: without them the database has no way to tell which first_name you mean.

SELECT e.first_name AS employee, m.first_name AS manager
FROM Employees e
LEFT JOIN Employees m ON m.emp_id = e.manager_id
ORDER BY manager, employee;

Note the LEFT JOIN. Five of the twelve employees in the sample data have no manager_id — they sit at the top of the tree. An INNER JOIN would quietly drop them; LEFT JOIN keeps all twelve and simply leaves the manager column empty for those five.

CROSS JOIN — every combination

A CROSS JOIN has no ON condition at all. It pairs every row on the left with every row on the right, so the result is the two row counts multiplied together. With 5 departments and 7 products, that's 35 rows:

SELECT d.dept_name, p.category
FROM Departments d
CROSS JOIN Products p;

It has genuine uses — building a grid of every month against every region, for instance, so that combinations with no data still show up as a row. But it is also what you get by accident if you list two tables and forget the join condition, which is why a query that should return a few hundred rows sometimes returns a few million.

JOIN vs UNION

These get confused because both combine two tables, but they work along different axes. A JOIN adds columns — it widens each row by matching related data side by side. A UNION adds rows — it stacks one result set on top of another, which requires both to have the same number of columns with compatible types.

If you want each order to also show the customer's company name, that is a JOIN. If you want one long list of every company name and every employee name together, that is a UNION.

Three JOIN mistakes worth knowing

Filtering an outer join in WHERE instead of ON

This is the one that catches people most often. A condition on the right-hand table placed in WHERE runs after the join, and it throws away the unmatched rows whose right-hand columns are NULL — silently turning your LEFT JOIN back into an INNER JOIN. If the condition belongs to the join, put it in ON.

Assuming the row count stays the same

A join is not a lookup. If one row on the left matches three rows on the right, you get three rows out. This is exactly why a SUM over a joined table sometimes comes back far too high — the same value got counted once per match.

Forgetting that NULL never matches NULL

Two NULLs are not equal to each other in SQL, so rows with a NULL in the join column never match anything, even another NULL. They will only survive if you are using an outer join.

Quick reference

A three-table join

Joins chain naturally — add as many as you need:

SELECT c.company, o.order_id, p.name AS product
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
JOIN Products p ON p.product_id = o.product_id;

Practice this topic

Reading explains it; solving it is what makes it stick. These exercises use exactly what this page covered, and your answer is checked by running it against the same database:

• Put a name to the department Medium
• Every department, even the quiet ones Hard
• Name the manager Hard
• Who is closing the deals? Hard

See all 42 exercises →

Frequently asked questions

Related reading

• UNION vs UNION ALL in SQL: Which One and Why
• Primary Key vs Foreign Key in SQL: The Difference Explained
• SQL Subqueries Explained: Nested SELECT Statements
• GROUP BY vs HAVING in SQL: What's the Difference?