/compare /sql/inner-join-vs-left-join

SQL INNER JOIN vs LEFT JOIN: which rows each one returns

updated 2026-10-20

tool: SQL
compares: INNER JOIN vs LEFT JOIN

INNER JOIN (or just JOIN) returns a row only when the join condition matches on both sides. LEFT JOIN (LEFT OUTER JOIN) returns every row of the left table; when the right side has no match, its columns are NULL. Use LEFT JOIN when the left rows must appear even without related rows, such as customers with zero orders.

Same data, both joins

bash

python - <<'EOF'
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
create table customers (id integer primary key, name text);
create table orders (id integer primary key, customer_id int, total int);
insert into customers values (1, 'Ada'), (2, 'Linus'), (3, 'Grace');
insert into orders values (10, 1, 30), (11, 1, 12), (12, 3, 50);
""")
def show(title, sql):
    print(f"-- {title}")
    for row in db.execute(sql):
        print(row)
show("INNER JOIN", "select c.name, o.total from customers c join orders o on o.customer_id = c.id order by c.id, o.id")
show("LEFT JOIN", "select c.name, o.total from customers c left join orders o on o.customer_id = c.id order by c.id, o.id")
show("LEFT JOIN + count", "select c.name, count(o.id) from customers c left join orders o on o.customer_id = c.id group by c.id order by c.id")
show("customers without orders", "select c.name from customers c left join orders o on o.customer_id = c.id where o.id is null")
EOF

Output

-- INNER JOIN
('Ada', 30)
('Ada', 12)
('Grace', 50)
-- LEFT JOIN
('Ada', 30)
('Ada', 12)
('Linus', None)
('Grace', 50)
-- LEFT JOIN + count
('Ada', 2)
('Linus', 0)
('Grace', 1)
-- customers without orders
('Linus',)

Linus has no orders: he is missing from the INNER JOIN, and appears with None (SQL NULL) in the LEFT JOIN.

Criterion by criterion

Criterion	INNER JOIN	LEFT JOIN
Rows returned	only matching pairs	all left rows, matched or not
Unmatched left rows	dropped	kept, right columns NULL
Condition on the right table in WHERE	normal filter	turns it into an inner join (NULL fails the test); put it in ON instead
count(right.id)	never 0	0 for rows without matches
Typical use	related data that must exist	optional data, reports with zero counts, anti-joins

Sources

glossary · sqlWhat is a database index?A database index is a sorted structure, usually a B-tree, that finds rows by column value without reading the whole table, at the cost of slower writes.compat · sqliteSQLite feature versions: RETURNING, STRICT, ->> and DROP COLUMNWhich SQLite version added what: DROP COLUMN 3.35, RETURNING 3.35, STRICT tables 3.37, the -> and ->> JSON operators 3.38, unixepoch() 3.38, concat_ws() 3.44.commands · postgresqlPostgreSQL upsert: INSERT … ON CONFLICT DO UPDATE and MERGEInsert a row or update it if the key exists with INSERT ... ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col, skip duplicates with DO NOTHING, or use MERGE.

Same data, both joins

Criterion by criterion

Sources

Related pages