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 and FULL JOIN are easy to mix up. The clearest way to see the difference is to keep the same two tables and the same query, change one word, and run it again.
- Customers and orders. We join customers to orders. Most customers have orders, but 7 never ordered, and 1 order has a customer ID that isn't in the customers table.
INNER JOIN
- INNER JOIN. Type the query. It selects first_name from customers (alias c) and order_id from orders (alias o), matches rows where o.customer_id equals c.customer_id, and sorts by order_id. It starts with INNER JOIN. Run it and you get 140 rows. INNER JOIN keeps only the rows that match on both sides, so customers with no orders and the order with no customer are left out.
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: replace INNER JOIN with LEFT JOIN. LEFT JOIN keeps every row from the first table, customers, even when a customer has no order. Run it again and you get 147 rows. That's 7 more, one for each customer who never ordered. In this lesson's database, the customers with no orders sort to the top, and row 1 is one of them (in PostgreSQL or Oracle they sort to the bottom instead). The first name is there, but order_id is NULL, which means no value. The customer stays in the result with nothing next to it.
LEFT JOIN
RIGHT JOIN
- RIGHT JOIN. Change LEFT JOIN to RIGHT JOIN. RIGHT JOIN keeps every row from the second table, orders, even when no customer matches. Run it and you get 141 rows: the 140 matches plus the 1 order whose customer doesn't exist. It sits at the bottom with a NULL first_name. The 7 customers with no orders are gone again.
RIGHT JOIN
FULL JOIN
- FULL JOIN. Last one: change RIGHT JOIN to FULL JOIN. FULL JOIN keeps every row from both tables, matched or not. FULL OUTER JOIN means the same thing. Run it and you get 148 rows: the 140 matches, plus the 7 customers with no order, plus the 1 order with no customer.
FULL JOIN - Read the count. Check the row count every time you change a join. Here it went 140, 147, 141 and 148, and each change shows you which rows were kept and which were dropped.
Recap
- The four joins. INNER keeps only matches. LEFT keeps all of the first table. RIGHT keeps all of the second. FULL keeps everything. In day-to-day work, INNER and LEFT are the ones you'll use most.
Common mistakes
- Filtering the right table in WHERE after a LEFT JOIN. A condition like WHERE o.order_id > 100 throws out the rows where order_id is NULL, so the LEFT JOIN quietly behaves like an INNER JOIN. Put that condition in the ON clause if you want to keep the customers with no orders.
- Mixing up which table is the left one. The left table is the one after FROM (customers here), and the right table is the one after JOIN (orders). Swap them and the same LEFT JOIN keeps a different set of rows.
- Expecting one row per customer. A customer with 3 orders shows up on 3 rows after the join, so the row count is not the number of customers. Use COUNT(DISTINCT c.customer_id) when you want to count customers.
- Using FULL JOIN in MySQL. MySQL doesn't support FULL JOIN. Run the LEFT JOIN, then add UNION ALL with the RIGHT JOIN filtered to WHERE c.customer_id IS NULL, so the matching rows aren't counted twice and real rows that happen to look the same aren't merged, which plain UNION would do.
When a business analyst uses this
In a data migration check, a BA LEFT JOINs the old system's customer table to the new one and looks for rows where the new system's ID is NULL. Those are the records that didn't migrate, and the count goes into the RAID log before sign-off.
Questions people ask
Why do we use joins in SQL?
Data is usually split across tables, like customers in one table and orders in another. A join puts them back together on a shared column, such as customer_id, so you can see each customer's name next to their orders.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only the rows that match in both tables. LEFT JOIN also keeps every row from the first table and fills the missing columns with NULL, which is why this example gave 147 rows instead of 140.
Is LEFT JOIN the same as LEFT OUTER JOIN?
Yes. The word OUTER is optional, so LEFT JOIN and LEFT OUTER JOIN give the same result. The same goes for RIGHT and FULL.
