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:
- Which products did each customer order? (The product is in
orders, but the customer's name is incustomers.) - Where are the pending orders going? (The status is in
orders, but the city and country are incustomers.)
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.