Write a query to display each order with the customer name?
Interview preparation resource from Gate Smashers.
SELECT o.order_id, c.customer_name, o.amount, o.status FROM Orders AS o JOIN Customers AS c ON o.customer_id = c.customer_id; -- Joins Orders to Customers using customer_id to display each order with the customer name. Use LEFT JOIN instead of JOIN if you need to include orders without a matching customer.
| customer_id | customer_name | city |
|---|---|---|
| 1 | Aditi Sharma | Delhi |
| 2 | Rahul Verma | Mumbai |
| 3 | Simran Kaur | Chandigarh |
| 4 | Arjun Nair | Bengaluru |
| order_id | customer_id | amount | status |
|---|---|---|---|
| 501 | 1 | 2400 | Delivered |
| 502 | 1 | 3200 | Delivered |
| 503 | 2 | 1800 | Delivered |
| 504 | 2 | 4100 | Pending |
| 505 | 3 | 950 | Cancelled |
SELECT o.order_id, c.customer_name, o.amount, o.status
FROM Orders AS o
JOIN Customers AS c ON o.customer_id = c.customer_id;Direct query
Use an equi-join between Orders and Customers on the customer_id column. The query returns order-level details together with the customer's name. See the SQL code block in the content blocks below for the exact statement.
Relationship key
customer_id is the relationship key linking the two tables: Orders.customer_id corresponds to Customers.customer_id.
- Key: customer_id ties Orders to Customers.
- Returned columns: order_id, customer_name, amount, status (as shown in the query).
Join behavior and when to use
The supplied query uses an INNER JOIN (written as JOIN) which returns only orders that have a matching customer row. If you need to preserve orders that lack a matching customer (orphaned orders), use a LEFT JOIN with Orders as the left table.
- INNER JOIN: Returns only orders with a matching customer.
- LEFT JOIN: Returns all orders; customer columns will be NULL where no matching customer exists.
Sample data
The following tables are the sample Customers and Orders data referenced by the query. Use them to verify the query's behavior.