SQL Inner Join
An inner join is a join that returns rows where two tables hold a matching value. The orders table stores a customer id, and the customers table gives that id a name.
Orders with customer names
The join follows the customer id stored on each order. Each order row picks up the name of its customer.
SELECT orders.order_id, customers.name, orders.quantity
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY orders.order_id;A join with a filter
WHERE still filters the joined rows. Only orders from customers in Austin stay in this result.
SELECT orders.order_id, customers.name, customers.city
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.city = 'Austin'
ORDER BY orders.order_id;Joining orders to products
The same join shape links orders to products. The match runs on the product id this time.
SELECT orders.order_id, products.name, orders.quantity
FROM orders
JOIN products ON orders.product_id = products.product_id
ORDER BY orders.order_id
LIMIT 4;Example
SELECT orders.order_id, customers.name, orders.order_date
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date BETWEEN '2024-03-01' AND '2024-03-31'
ORDER BY orders.order_date, orders.order_id;Key points
- JOIN: a clause that links rows from two tables into one result
- ON: the clause that names the matching columns for a join
- Use table.column to name a column from one of the joined tables.
- A row with no matching row on the other side drops out of the result.
- A join pairs each row with the matching row from the other table.
In this example, the join links each order to its customer first. The WHERE clause then keeps the orders placed in March. The result shows five March orders, and each one carries a customer name instead of a bare id.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
What does the ON clause do in a join?