INNER JOIN Matching Rows

Easy4 min Save for later

Question

Interview insights are lockedSee how often this is asked and which companies asked it with Buncho Pro.

Problem

You have customers and orders.

Table: customers

id	name
1	Ana
2	Raj
3	Mei

Table: orders

id	customer_id	total
101	1	50
102	1	30
103	2	70

Task

Write a query that returns only the rows where a customer has a matching order: the customer name, the order id and the total.

Expected output

name	order_id	total
Ana	101	50
Ana	102	30
Raj	103	70

Work out your query on your own, then open Answers & Optimization to compare.

Hints

Answers

2 ways to solve it

A basic answer first, then the optimized one.

Basic
Optimized

Quick answer

INNER JOIN keeps only rows with a match on the join condition. Mei has no orders, so she is dropped.
ON handles any condition. USING (col) is a shorthand when both columns share a name.

1
1Basic
INNER JOIN with ON
```
1SELECT *2FROM customers3INNER JOIN orders ON customers.id = orders.customer_id;
```
- Problem: SELECT * returns both id columns and every column you do not need

2Optimized

Aliases, needed columns, indexed key

1CREATE INDEX idx_orders_customer_id ON orders (customer_id);2 3SELECT c.name, o.id AS order_id, o.total4FROM customers c5JOIN orders o ON o.customer_id = c.id;6 7-- Only customer columns needed? A semi-join returns each customer once, no duplicates8SELECT c.id, c.name9FROM customers c10WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Dialects: USING (col) works in PostgreSQL, MySQL and Oracle, not in SQL Server
JOIN alone means INNER JOIN

Spotted a mistake in these answers?

Report a problem