SQL INNER JOIN Explained With a Simple Example
An INNER JOIN combines two tables where a key matches. Join orders to customers to show each order with the customer's name, and see why one order disappears from the result.
Step by step
Why join
- Two tables, one answer. Your manager wants a list of orders with each customer's name. But orders and customers live in two different tables. Inner join puts them together. Let's see how.
- The orders table. The orders table only keeps a customer ID, a number. Not the name. Let's look. The order ID, the customer ID and the total, from orders. A hundred and forty one orders.
SELECT order_id, customer_id, order_total FROM orders; - Just a number. These customer I Ds are just numbers. The names are in the customers table, and it has the same customer ID column.
Write the join
- Short names. Start again, from orders as o. The o is a short name for the table, so o dot order total means the total from orders. And c will be customers.
SELECT o.order_id, c.first_name, o.order_total FROM orders AS o - INNER JOIN. Next, inner join customers as c. That brings in the second table.
INNER JOIN customers AS c - Match on the key. And on tells SQL how the rows match. The customer ID in orders equals the customer ID in customers. It's the key both tables share.
ON o.customer_id = c.customer_id; - Orders with names. Run it. Now every order shows the customer's first name.
- Names from customers. These names came from the customers table, matched row by row on the customer ID.
The missing order
- One order missing. But look at the count. A hundred and forty, not a hundred and forty one. One order points at a customer that doesn't exist, so inner join leaves it out.
Recap
- The INNER JOIN rule. So, inner join keeps only the rows that match in both tables. Name both tables, then say which columns match with on. Next, left join keeps the rows that don't match.
