SQL Joins Explained: INNER vs LEFT vs RIGHT vs FULL
Inner, left, right and full joins on the same two tables, customers and orders. Change one word, run the query again, and watch the row count tell you which rows each join keeps.
Step by step
Four joins
- Which join?. Inner, left, right, full. Four joins, and people mix them up all the time. The trick is to use the same two tables and change one word. Let's try all four.
- Customers and orders. We'll join customers to orders. Most customers have orders, but a few never ordered, and one order has a customer that doesn't exist.
INNER JOIN
- INNER JOIN. Here's the query. First name from customers, order ID from orders, matched on the customer ID, sorted by order ID. We start with inner join. A hundred and forty rows. Inner join keeps only the rows that match on both sides.
SELECT c.first_name, o.order_id FROM customers AS c INNER JOIN orders AS o ON o.customer_id = c.customer_id ORDER BY o.order_id;
LEFT JOIN
- LEFT JOIN. Now change one word. Inner becomes left. Left join keeps every row from the first table, customers, even with no order. A hundred and forty seven. That's seven more rows, one for each customer who never ordered. Here's one at the top. The order ID is null, which means no value. The customer stayed, with nothing next to it.
LEFT JOIN
RIGHT JOIN
- RIGHT JOIN. Change left to right. Right join keeps every row from the second table, orders, even when no customer matches. A hundred and forty one. Every order is back, including the one whose customer is missing. It sits at the bottom, with a null first name.
RIGHT JOIN
FULL JOIN
- FULL JOIN. Last one. Change right to full. Full join keeps everything from both tables, matched or not. A hundred and forty eight. The matches, plus the seven customers with no order, plus the one order with no customer.
FULL JOIN - Read the count. Watch this number every time you change a join. It tells you straight away what was kept and what was dropped.
Recap
- The four joins. So, inner keeps only matches. Left keeps all of the first table. Right keeps all of the second. Full keeps everything. Most of the time you'll use inner or left.
