Joins

Real data rarely lives in one table. A company's employees live in one table, their departments in another, their expense reports in a third. A join is how you combine rows from more than one table based on a related column, so a single query can answer a question that spans all of them.

Learning Objectives

Combine two tables with INNER JOIN based on a matching column.
Use LEFT JOIN to keep rows from the first table even when there's no match in the second.
Choose correctly between INNER JOIN and LEFT JOIN for a given question.

Sample Table: employees

id	first_name	department_id
1	Ada	1
2	Grace	1
3	Margaret	2
4	Hedy	NULL

Sample Table: departments

employees.department_id points at departments.id, that relationship is what a join actually matches on. Notice Hedy has no department_id, and Marketing has no employees yet, both matter for how different join types behave.

id	name
1	Engineering
2	Research
3	Marketing

Two Tables

employees + departments

Matched on a Column

department_id = id

Combined Rows

One result, columns from both

INNER JOIN

INNER JOIN returns only rows that have a match on both sides. Hedy is missing from the result, she has no department_id to match against. Marketing is missing too, nothing in employees points at it. INNER JOIN syntax and behavior are identical across every major database.

MySQL / PostgreSQL / SQLite / SQL Server

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

-- Result:
-- first_name | department
-- Ada        | Engineering
-- Grace      | Engineering
-- Margaret   | Research

NOTE

The word INNER is optional, plain JOIN by itself means exactly the same thing as INNER JOIN on every database in this course. Many real-world queries just write JOIN. LEFT, RIGHT, and FULL always need their keyword written out, only INNER has this shorthand.

LEFT JOIN

LEFT JOIN keeps every row from the left-hand table (employees) regardless of whether a match exists on the right. Hedy now appears, with NULL in place of a department, since she has none. The table named first in FROM is always the "left" table.

MySQL / PostgreSQL / SQLite / SQL Server

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

-- Result:
-- first_name | department
-- Ada        | Engineering
-- Grace      | Engineering
-- Margaret   | Research
-- Hedy       | NULL

Choosing Between INNER JOIN and LEFT JOIN

INNER JOIN

"Give me only rows that have a match on both sides"
A row with no match disappears from the result entirely
Right choice when the relationship is required

LEFT JOIN

"Give me every row from the first table, matched where possible"
A row with no match still appears, with NULL for the missing side
Right choice when the relationship is optional

Joining More Than Two Tables

Each additional JOIN adds another table, with its own ON condition. The order they're written in doesn't change the result, as long as every join's ON condition correctly relates to a table already available to it.

MySQL / PostgreSQL / SQLite / SQL Server

SELECT employees.first_name, departments.name AS department, projects.title
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN projects ON projects.employee_id = employees.id;

RIGHT JOIN

RIGHT JOIN is the mirror image of LEFT JOIN, it keeps every row from the right-hand table (departments) regardless of a match, and fills in NULL on the left side where there's none. Marketing now appears even though no employee works there. In practice, most developers just swap the table order and use LEFT JOIN instead, so RIGHT JOIN is rarely seen in real code, even though every database here supports it.

MySQL / PostgreSQL / SQLite (3.39+) / SQL Server

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

-- Result:
-- first_name | department
-- Ada        | Engineering
-- Grace      | Engineering
-- Margaret   | Research
-- NULL       | Marketing

FULL JOIN

FULL JOIN keeps unmatched rows from both sides at once, Hedy (no department) and Marketing (no employees) both show up, each with NULL on the side that has no match. MySQL is the one database here with no native FULL JOIN keyword, it has to be built by UNIONing a LEFT JOIN and a RIGHT JOIN together, which UNION (not UNION ALL) automatically de-duplicates the rows that matched on both sides.

PostgreSQL / SQLite (3.39+) / SQL Server

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

-- Result:
-- first_name | department
-- Ada        | Engineering
-- Grace      | Engineering
-- Margaret   | Research
-- Hedy       | NULL
-- NULL       | Marketing

MySQL (no native FULL JOIN, simulated)

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

Frequently Asked Questions

What happens if I forget the ON condition?

Without ON, some databases either reject the query or produce a cross join, every row from the first table paired with every row from the second, which is almost never what you want and can produce an enormous, meaningless result on real tables.

Can I join on more than one column?

Yes, ON table_a.col1 = table_b.col1 AND table_a.col2 = table_b.col2 is valid, useful when a relationship is defined by a combination of columns rather than a single key.

Does the order of INNER JOIN vs LEFT JOIN in the FROM clause matter for performance?

Modern query optimizers in MySQL, PostgreSQL, SQL Server, and SQLite generally reorder joins internally for efficiency regardless of how you wrote them, so write joins in whatever order reads most clearly, correctness matters far more than the written order here.

Is a query that just says JOIN, with no INNER/LEFT/RIGHT/FULL, missing something?

No, plain JOIN is shorthand for INNER JOIN specifically, and it's extremely common in real code. LEFT, RIGHT, and FULL always have to be spelled out, there's no shorthand for them, only INNER gets to be implied.

Common Beginner Mistakes

Using INNER JOIN when the relationship is actually optional

If some employees genuinely have no department, INNER JOIN silently drops them from every report that uses it. LEFT JOIN is the right tool whenever "no match" is a valid, expected case you still want visible.

Forgetting the table alias after switching to LEFT JOIN, and filtering out the very rows LEFT JOIN was meant to keep

WHERE departments.name = 'Engineering' after a LEFT JOIN silently turns it back into an INNER JOIN, because WHERE discards rows where departments.name is NULL, exactly the unmatched rows LEFT JOIN was supposed to preserve. Filtering conditions on the joined table usually belong in the ON clause instead, if the intent is to keep unmatched rows.

Confusing which table is "left" when the FROM clause has several joins chained together

The "left" side of any individual JOIN is everything already combined before it in the query, not necessarily the very first table named in FROM. Read joins top to bottom to reason about what's already in play at each one.

Best Practices

Always write an explicit ON condition, never rely on implicit joins via a comma-separated FROM list.
Default to INNER JOIN, and switch to LEFT JOIN deliberately, only once you've confirmed the relationship is genuinely optional.
Qualify column names with their table (employees.first_name) once a query joins more than one table, for clarity.
Put conditions that should affect which rows exist in the result inside ON, not WHERE, when working with LEFT JOIN.

Interview Questions

Write a query listing every employee and their department name, including employees with no department.

SELECT employees.first_name, departments.name AS department FROM employees LEFT JOIN departments ON employees.department_id = departments.id; — LEFT JOIN keeps every employee row, with NULL for department where there's no match.

Why does adding WHERE departments.name = 'Engineering' after a LEFT JOIN effectively turn it into an INNER JOIN?

WHERE filters the final result set after the join has already happened, and it discards any row where departments.name is NULL, since that condition can't be true for a NULL value. That eliminates exactly the unmatched rows LEFT JOIN exists to preserve, so in practice the two queries end up returning the same rows as an INNER JOIN would.

Which join type supports getting rows from both tables that don't have a match on either side, and which major database lacks native support for it?

FULL JOIN (also called FULL OUTER JOIN) returns unmatched rows from both the left and right tables at once. MySQL doesn't support it directly, it has to be simulated with a UNION of a LEFT JOIN and a RIGHT JOIN.

Summary

This lesson covered combining rows across tables with INNER JOIN (matches only) and LEFT JOIN (every row from the first table, matched where possible), how to tell which one a question actually calls for, and briefly, RIGHT JOIN and FULL JOIN support across databases.

What's Next?

The final lesson in this module covers subqueries, a query nested inside another query, and how they compare to the joins covered here.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.