Joins
Joining related tables with INNER JOIN
The orders table stores information about each order, including the ID of the customer who placed it.
Run the following query to look at the first five orders:
1234SELECT id, customer_id, total_amountFROM ordersORDER BY idLIMIT 5;You should see:
1234567 id | customer_id | total_amount----+-------------+-------------- 1 | 2 | 10.01 2 | 3 | 10.02 3 | 4 | 10.03 4 | 5 | 10.04 5 | 6 | 10.05The customer_id tells us which customer placed each order.
The customer's details, however, are stored in the customers table. Run:
1234SELECT id, full_name, emailFROM customersWHERE id BETWEEN 2 AND 6ORDER BY id;You should see:
1234567 id | full_name | email----+-------------+----------------------- 2 | Customer 2 | Customer2@Example.com 3 | Customer 3 | Customer3@Example.com 4 | Customer 4 | Customer4@Example.com 5 | Customer 5 | Customer5@Example.com 6 | Customer 6 | Customer6@Example.comThe two tables are related through orders.customer_id and customers.id. For example, the value 2 in orders.customer_id matches the row where customers.id is 2.
A join lets us combine these related rows in a single query.
Using INNER JOIN
Suppose we want to see each order together with the name and email address of the customer who placed it.
We can use an INNER JOIN:
12345678910SELECT orders.id AS order_id, orders.total_amount, customers.full_name, customers.emailFROM ordersINNER JOIN customers ON orders.customer_id = customers.idWHERE orders.id <= 5ORDER BY orders.id;You should see:
1234567 order_id | total_amount | full_name | email----------+--------------+-------------+----------------------- 1 | 10.01 | Customer 2 | Customer2@Example.com 2 | 10.02 | Customer 3 | Customer3@Example.com 3 | 10.03 | Customer 4 | Customer4@Example.com 4 | 10.04 | Customer 5 | Customer5@Example.com 5 | 10.05 | Customer 6 | Customer6@Example.comPostgreSQL has combined columns from the matching rows in orders and customers.
The part that determines which rows match is:
1ON orders.customer_id = customers.idThis is the join condition.
For each order, PostgreSQL matches it with the customer whose id equals the order's customer_id.
A simplified view looks like this:
This is the central idea behind a join: PostgreSQL uses the join condition to determine which rows from the two tables belong together.
Why is it called an INNER JOIN?
An INNER JOIN returns only rows for which the join condition finds a match in both tables.
In our query, an order is included when its customer_id matches an id in the customers table. Rows without a match are not included in the result.
We'll see how to keep rows that don't have a match when we look at LEFT JOIN in the next chapter.
Using table aliases
Join queries often refer to the same table names several times. We can make them shorter by giving each table an alias.
Instead of:
12345678910SELECT orders.id AS order_id, orders.total_amount, customers.full_name, customers.emailFROM ordersINNER JOIN customers ON orders.customer_id = customers.idWHERE orders.id <= 5ORDER BY orders.id;we can write:
12345678910SELECT o.id AS order_id, o.total_amount, c.full_name, c.emailFROM orders AS oINNER JOIN customers AS c ON o.customer_id = c.idWHERE o.id <= 5ORDER BY o.id;Here, o is an alias for orders and c is an alias for customers.
The query works exactly the same way, but the shorter names make join queries easier to read.
You will commonly see the AS keyword omitted when creating table aliases:
123FROM orders oINNER JOIN customers c ON o.customer_id = c.idBoth forms mean the same thing.
INNER is optional
For an inner join, the INNER keyword is optional.
These two queries mean the same thing:
123FROM orders oINNER JOIN customers c ON o.customer_id = c.id123FROM orders oJOIN customers c ON o.customer_id = c.idWhen you write JOIN without specifying a join type, PostgreSQL treats it as an INNER JOIN.