Schema and sample data
| customer_id | name | city |
|---|---|---|
| 1 | Alice Morgan | Austin |
| 2 | Bimal Shah | Denver |
| 3 | Chen Wei | Austin |
| 4 | Diego Ramos | Miami |
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 120.00 |
| 102 | 3 | 75.50 |
| 103 | 1 | 60.00 |
| 104 | 2 | 210.00 |
| 105 | 3 | 45.00 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(30)
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
INSERT INTO Customers (customer_id, name, city) VALUES
(1, 'Alice Morgan', 'Austin'),
(2, 'Bimal Shah', 'Denver'),
(3, 'Chen Wei', 'Austin'),
(4, 'Diego Ramos', 'Miami');
INSERT INTO Orders (order_id, customer_id, amount) VALUES
(101, 1, 120.00),
(102, 3, 75.50),
(103, 1, 60.00),
(104, 2, 210.00),
(105, 3, 45.00);You have two tables: Customers holds one row per customer, and Orders holds one row per order with a customer_id that points back to the customer who placed it.
Return one row for every order, showing the customer name, the order_id and the order amount. Join the two tables on customer_id using an inner join. Sort the result by name, then by order_id.
Expected result
| name | order_id | amount |
|---|---|---|
| Alice Morgan | 101 | 120.00 |
| Alice Morgan | 103 | 60.00 |
| Bimal Shah | 104 | 210.00 |
| Chen Wei | 102 | 75.50 |
| Chen Wei | 105 | 45.00 |
Show hint
Use INNER JOIN to match each order to its customer on customer_id, then ORDER BY two columns. Because it is an inner join, a customer who has placed no orders has nothing to match and simply drops out of the result.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name, o.order_id, o.amount
FROM Customers AS c
INNER JOIN Orders AS o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;The INNER JOIN pairs each row in Orders with the matching row in Customers where the two customer_id values are equal. Every order belongs to exactly one customer, so the join produces one output row per order, now carrying the customer name alongside the order fields. Table aliases (c and o) keep the column references short and unambiguous. See how SQL joins work for the full picture.
The key idea of an inner join is that it keeps only rows that match on both sides. Diego Ramos exists in Customers but has no rows in Orders, so there is nothing to join to and he never appears in the result. That is the intended behaviour here. If you wanted to list every customer including those with zero orders, you would switch to a LEFT JOIN and the unmatched customer would show NULL for the order columns.
ORDER BY c.name, o.order_id sorts first alphabetically by name, then breaks ties by order id, which is why Alice Morgan's two orders (101 then 103) come out in ascending id order. A common mistake is to forget the second sort key: without o.order_id the rows within a single customer could come back in any order, since SQL does not guarantee ordering unless you ask for it.