Orders from US Customers — INNER JOIN in SQL

The problem

Brightlane's regional sales team is preparing a territory analysis and needs to isolate orders from the US market.

Write a query to return the customer name and order total for every order placed by a US-based customer.

Assumptions:

The orders table contains every order Brightlane has processed.
The customers table contains every customer Brightlane has on file; US customers are identified by country = 'US' on the customer record.
The country condition applies to the customer record, not to anything stored on the order itself.

Output:

One row per qualifying order, with columns customer_name and total_amount.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.name AS customer_name,
  o.total_amount
FROM
  orders o
  JOIN customers c ON o.customer_id = c.id
WHERE
  c.country = 'US'

The shape

country lives on the customer record, not on the order, so the territory filter has to ride on top of the join. The JOIN first pairs each order with its customer, and the WHERE then narrows the assembled rows to the ones whose customer side has country = 'US'.

Clause by clause

FROM orders o reads from the orders table and aliases it as o.
JOIN customers c ON o.customer_id = c.id pairs each order with its customer. Every order has a valid customer_id, so every order passes through with the customer's columns attached.
WHERE c.country = 'US' filters the joined rows. The condition references c.country — a column from the customers side — and the qualifier matters because both tables carry several columns and the join has put them all in scope. PostgreSQL evaluates the condition row by row against the combined result, keeping the order only when its paired customer is US-based. Orders by Alice Nguyen and Bob Chen come through; orders by anyone outside the US drop.
SELECT c.name AS customer_name, o.total_amount returns the two columns the territory report needs: the customer's name from the customers side, the order's total from the orders side.

Why this and not filter on the orders side

The orders table doesn't store country. That attribute belongs to the customer. There is no o.country column to filter on; the only way to express "orders by US-based customers" is to bring the customer's country into scope through the join, then filter on it. This is the general pattern: when the criterion lives on a related table, join first, filter after.

You practiced applying a condition on a column from one of the matched-in tables. WHERE c.country = 'US' reaches across the assembled rows — once two tables are connected, every column from either side is fair game in the condition.

Return the customer name and order total for every order placed by a US-based customer

The shape

Clause by clause

Why this and not filter on the orders side

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.