LEFT JOIN

A LEFT JOIN returns every row from the first table, then attaches matching information from the second table wherever a match exists - gaps just show up empty rather than disappearing.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Keeping All Retail Orders with LEFT JOIN

SELECT o.order_id, o.revenue, c.region, c.channel
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id

Result:

order_id	revenue	region	channel
O2001	129.99	Northeast	Online
O2002	54.5	Southeast	In-Store
O2003	210.0	West	Online
O2004	89.75	Midwest	Mobile
O2005	340.0

Step by step

After SELECT, list the column names you want, separated by commas.
```
SELECT o.order_id, o.revenue, c.region, c.channel
```

FROM tells SQL which table to pull the data from.

SELECT o.order_id, o.revenue, c.region, c.channel
FROM orders o

LEFT JOIN keeps every row from the left table, even when no match exists.

SELECT o.order_id, o.revenue, c.region, c.channel
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id;

Practice

Try each one in your head first, then open the answer.

1. Write a query that returns every row from orders and matching rows from customers on customer_id, including orders rows with no match.

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

2. Left join orders to returns on order_id so every order appears - including orders with no return.

SELECT * FROM orders LEFT JOIN returns ON orders.order_id = returns.order_id

3. Look at the products and reviews tables. Find the column they share, then write a LEFT JOIN so every product appears - even those with no matching review.

SELECT * FROM products LEFT JOIN reviews ON products.product_id = reviews.product_id

4. Examine the orders and shipments tables. Identify their shared column, then write a LEFT JOIN so every order appears - even those not yet shipped.

SELECT * FROM orders LEFT JOIN shipments ON orders.order_id = shipments.order_id

5. Look at the suppliers and supplier_products tables. Find the column they have in common, then write a LEFT JOIN keeping all suppliers - even those with no products listed.

SELECT * FROM suppliers LEFT JOIN supplier_products ON suppliers.supplier_id = supplier_products.supplier_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed