Multiple JOINs in SQL

Level 5258 keystrokes

Multiple JOINs connect several tables using foreign key relationships.

SQL code

-- Return order id, customer, product, quantity and price in one row
1SELECT
2 o.id,
-- Both tables have a name column, so aliases tell them apart
3 c.name AS customer,
4 p.name AS product,
5 oi.quantity,
6 oi.price
-- Start from orders (alias o)
7FROM
8 orders o
-- Add the customer who placed each order
9INNER JOIN
10 customers c ON o.customer_id = c.id
-- Add the line items: an order with 2 items becomes 2 rows
11INNER JOIN
12 order_items oi ON o.id = oi.order_id
-- Add the product of each line item
-- INNER JOIN drops a row if any link is missing
13INNER JOIN
14 products p ON oi.product_id = p.id
-- Only completed orders
15WHERE
16 o.status = 'completed';

Output

+----+----------+----------+----------+-------+
| id | customer | product  | quantity | price |
+----+----------+----------+----------+-------+
|  1 | Alice    | Laptop   |        1 | 999.0 |
|  1 | Alice    | Mouse    |        2 |  25.0 |
|  2 | Bob      | Keyboard |        1 |  75.0 |
+----+----------+----------+----------+-------+
3 rows in set
Practice typing this code

Practice typing this code. Results are not recorded in the rankings.

Symbols in this code

The symbols you type in this SQL code and how often they appear.

  • .× 12
  • _× 4
  • ,× 4
  • =× 4
  • '× 2
  • ;× 1

More SQL examples at level 5

← SQL typing practice