Orders with Customer Names — INNER JOIN in SQL

The problem

Brightlane's customer service team is assembling a case-management report and needs order records matched to the customers who placed them.

Write a query to return the customer name, order ID, and order total for every order.

Assumptions:

Every order has a valid customer_id, so every order will appear in the result.

Output:

One row per order, with columns customer_name, order_id, 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.id AS order_id,
  o.total_amount
FROM
  orders o
  JOIN customers c ON o.customer_id = c.id

The shape

The orders table carries a customer_id reference, and the customers table carries the matching id. JOIN ... ON o.customer_id = c.id pairs each order with its customer, so a single row in the result holds the order ID, the total, and the customer's name side by side.

Clause by clause

FROM orders o reads from the orders table and aliases it as o. The aliasing matters here because orders and customers both have an id column, and unqualified references to id would be ambiguous.
JOIN customers c ON o.customer_id = c.id is the assembly. For each row in orders, PostgreSQL finds the row in customers whose id equals that order's customer_id, and emits a combined row. Because every order has a valid customer_id, every order appears exactly once in the result. 200 orders in, 200 rows out.
SELECT c.name AS customer_name, o.id AS order_id, o.total_amount picks three columns from the combined row: the customer's name from the customers side, the order's ID from the orders side, and the total from the order. Every column reference is prefixed by its alias, and the two ID columns get explicit aliases (customer_name, order_id) so the result row reads as a case-management report rather than two unqualified id columns next to each other.

You practiced an INNER JOIN between two tables on a foreign-key/primary-key match. The recurring shape: when a fact (an order) holds a reference to a parent entity (a customer) and the report needs columns from both, JOIN ... ON foreign_key = primary_key is the assembly mechanism.

Return the customer name, order ID, and order total for every order

The shape

Clause by clause

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