JOIN: Combine Data from Two Tables

In SQL, a JOIN combines rows from two or more tables, based on a column they have in common.

Joins are helpful when your data is spread across several tables, like customers and their orders, and you want to see it together in one result.


Example Scenario

For the joins lessons, we use two new tables, customers and orders:

SELECT * FROM customers;

Output:

 customer_id |  name  |    city     | country 
-------------+--------+-------------+---------
           1 | Alice  | New York    | USA
           2 | Bob    | Los Angeles | USA
           3 | Emma   | London      | UK
           4 | Paul   | Berlin      | Germany
           5 | Olivia | Madrid      | Spain
(5 rows)
SELECT * FROM orders;

Output:

 order_id | customer_id | product_name | order_status | order_date 
----------+-------------+--------------+--------------+------------
      101 |           1 | Laptop       | Delivered    | 2024-01-15
      102 |           1 | Phone        | Shipped      | 2024-02-03
      103 |           2 | Charger      | Cancelled    | 2024-02-20
      104 |           3 | Watch        | Delivered    | 2024-03-11
      105 |           4 | Headphones   | Shipped      | 2024-03-28
      106 |           4 | Speakers     | Pending      | 2024-04-02
      107 |             | Tablet       | Incomplete   | 2024-04-15
(7 rows)

Each order has a customer_id. It tells you which customer placed the order. For example, orders 101 and 102 have customer_id 1, so Alice placed them.

Now imagine these questions:

  1. Which products did each customer order? (The product is in orders, but the customer's name is in customers.)
  2. Where are the pending orders going? (The status is in orders, but the city and country are in customers.)

To answer them, you need data from both tables at once. That is what a join does.

Note: Take a moment to understand these two tables. All the joins lessons use them.


Basic JOIN Syntax

SELECT table1.column_name, table2.column_name
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

Example Query

SELECT customers.name, orders.product_name
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;

Output:

 name  | product_name 
-------+--------------
 Alice | Laptop
 Alice | Phone
 Bob   | Charger
 Emma  | Watch
 Paul  | Headphones
 Paul  | Speakers
(6 rows)

This joins the two tables where customers.customer_id matches orders.customer_id. The result shows a name from customers next to a product from orders.

When two tables have a column with the same name, like customer_id or name, write the table name before it, like customers.name. Then PostgreSQL knows which one you mean.


In the next lessons, you will learn the different types of joins, like INNER JOIN and LEFT JOIN, and when to use each one.

In the exercise, fill in the ON condition to join the two tables.