Lesson 5.1

Joining Customers to Orders in SQL

A join lets one result use columns from more than one table.

Real datasets are usually split into related tables. The orders table knows which customer placed an order, but the customer name lives in customers.

A JOIN matches rows using a shared key. Here, both tables use customer_id.

In orders, customer_id isn't a new customer. It points back at the one row in customers that placed the order, and the join follows that pointer. The short names after as, o and c, are table aliases: o.customer_id means the customer_id column of orders, which matters once two tables share a column name.

Follow the key, then filter

Build the join by matching the key columns first, then add filters after the relationship is working. In this lesson, the join creates rows that contain both order details and customer details, so the state filter can come from the customer table.

Pattern

select a.id_column, b.detail_column
from table_a as a
join table_b as b
  on a.shared_id = b.shared_id

Leave the key match out and every order pairs with every customer: see why a JOIN without ON multiplies rows.

Syntax reference

Need syntax details? See the SQL JOIN types reference

Schema · Garden Shop Tables · orders, customers 2 tables

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Your task

Join orders to customers. Return the order id, order date, customer first name, customer last name, and order status for orders from PA customers, newest first.