SQL Joins: INNER and LEFT JOIN

A join combines rows from two or more tables based on a related column between them, most often a foreign key matching a primary key. Joins are what let a normalized database — split into small, non-redundant tables — be reassembled back into the combined view an application or report actually needs.

Why it matters

Normalised data is spread across tables, and joins are how it is put back together: orders with their customers, students with their courses. Joins are the heart of SQL and the topic interviewers test most. The classic mistake is an inner join that silently drops rows that have no match.

INNER JOIN

INNER JOIN returns only the rows that have a match in both tables. If a row in the left table has no corresponding row in the right table (based on the join condition), that row is excluded entirely from the result. INNER JOIN is the default and most commonly used join type when you only care about complete, matched pairs.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns every row from the left table, whether or not it has a match in the right table. When there is no match, the columns from the right table are filled with NULL for that row. This makes LEFT JOIN the right tool whenever you need to include "rows with nothing related yet" — for example, listing every customer including those who have never placed an order.

The Practical Difference in Results

Given a customers table and an orders table, an INNER JOIN between them returns only customers who have placed at least one order — a customer with zero orders simply disappears from the result. A LEFT JOIN from customers to orders returns every customer, and for any customer with no orders, the order columns appear as NULL instead of the customer row being dropped. Choosing the wrong join type is a very common source of "missing rows" bugs in reports.

Examples

SELECT c.name, o.id AS order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Returns 2 rows: ('Asha Rao', 101), ('Rohit Sharma', 102) — a customer named 'Meera Iyer' with zero orders does not appear.

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Returns 3 rows: ('Asha Rao', 101), ('Rohit Sharma', 102), ('Meera Iyer', NULL) — Meera now appears with a NULL order_id.

Exercise

List every customer with the products they have ordered. A customer with no orders must still appear, with NULL as the product. Order the result by customer name and then by product.

Expected output: name | product, Asha | book, Asha | pen, Ravi | NULL (one per line)

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), product TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders (id, customer_id, product) VALUES (1, 1, 'pen'), (2, 1, 'book');

-- TODO: every customer with their products, including customers with no orders

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), product TEXT);
INSERT INTO customers (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders (id, customer_id, product) VALUES (1, 1, 'pen'), (2, 1, 'book');

SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.product;

Quick quiz

Interview questions