SQL JOIN

Q: What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns all rows from the left table and fills the right table's columns with NULL when there is no match.

Pronunciation: ES-kyoo-EL JOYN or SEE-kwul JOYN

Updated September 30, 20262 min read

In short

A SQL JOIN is a query operation that combines rows from two or more tables into one result, matching them on related columns such as a foreign key.

What is a SQL JOIN?

A JOIN combines data that is spread across several tables into a single result. In a well-designed relational database, customers and their orders live in separate tables, and a JOIN matches each order to its customer by comparing a column in one table, such as orders.customer_id, with a column in the other, such as customers.id. The condition that decides which rows match is written after the ON keyword.

There are several kinds of JOIN. An INNER JOIN, which is what you get when you just write JOIN, returns only rows that have a match in both tables. A LEFT JOIN returns every row from the left table plus matching rows from the right, filling the gaps with NULL; RIGHT JOIN does the reverse, FULL OUTER JOIN keeps unmatched rows from both sides, and CROSS JOIN pairs every row of one table with every row of the other.

Picture two guest lists for a party: an inner join lists only the people who appear on both lists, while a left join lists everyone on the first list and notes who is also on the second. JOINs are also what make normalization practical, because data can be stored once, without duplication, and reassembled at query time.

A common mistake is using an inner join where a left join is needed, which silently drops rows, for example customers who have never ordered. Another is a missing or wrong join condition, which multiplies rows and inflates totals. JOIN is also different from UNION: a JOIN places columns from matching rows side by side, while UNION stacks the rows of two queries on top of each other.

At a glance

INNER JOIN keeps only rows that match in both tables. LEFT JOIN also keeps every row from the left table and fills the gaps with NULL.

Key takeaways

A JOIN combines rows from multiple tables based on a matching condition.
INNER JOIN keeps only rows that match in both tables.
LEFT JOIN keeps every row from the left table, with NULL where nothing matches.
Joins usually follow foreign key relationships.
Index the join columns to keep joins on large tables fast.

Example

INNER JOIN versus LEFT JOINsql

-- INNER JOIN: only customers who have placed at least one order
SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

-- LEFT JOIN: every customer, including those with no orders (count 0)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Readers ask

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns all rows from the left table and fills the right table's columns with NULL when there is no match.

Are SQL JOINs slow?

Not when the join columns are indexed; databases are built to join millions of rows efficiently. Joins become slow when those columns lack indexes, when a query joins many large tables, or when a missing condition creates a huge number of row combinations.

What is a self join?

A self join joins a table to itself using two different aliases. It is useful for hierarchical data, such as an employees table where each row has a manager_id pointing to another employee.