Learn how to join tables in PostgreSQL with clear examples. Understand INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and how to combine related data from multiple tables in SQL.
In a database, information is usually divided into different tables.
For example, in the DVD Rental database:
customer stores customer information.payment stores payment information.rental stores rental information.film stores film information.inventory stores available copies of films.staff stores staff information.These tables are related through keys.
For example:
customer
---------
customer_id ←──────────────┐
first_name │
last_name │
│
payment │
--------- │
payment_id │
customer_id ────────────────┘
amount
payment_date
A JOIN allows us to combine related information from two or more tables.
JOIN is used to retrieve related data from multiple tables.
Suppose we run:
SELECT *
FROM customer;
We get customer information.
If we run:
SELECT *
FROM payment;
We get payment information.
But suppose we want to know:
Which customer made each payment?
The payment table contains customer_id, but we also need the customer’s name from the customer table.
We can JOIN the tables:
SELECT
customer.first_name,
customer.last_name,
payment.amount
FROM customer
JOIN payment
ON customer.customer_id = payment.customer_id;
Result will look conceptually like:
| first_name | last_name | amount |
|---|---|---|
| Mary | Smith | 7.99 |
| Patricia | Johnson | 4.99 |
| Linda | Williams | 2.99 |
The JOIN connects:
customer.customer_id
=
payment.customer_id
Students should understand these two terms:
A Primary Key (PK) uniquely identifies a record.
Example:
customer
----------------
customer_id PK
first_name
last_name
customer_id uniquely identifies each customer.
A Foreign Key (FK) refers to a primary key in another table.
Example:
payment
----------------
payment_id
customer_id FK
amount
Here:
payment.customer_id
↓
customer.customer_id
This relationship allows us to JOIN the tables.
SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;
Example:
SELECT
customer.first_name,
customer.last_name,
payment.amount
FROM customer
JOIN payment
ON customer.customer_id = payment.customer_id;
The important JOIN types in PostgreSQL are:
DVD Rental database will be used for practical examples.
An alias gives a table a short name.
Instead of:
customer.customer_id
we can write:
c.customer_id
Example:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer AS c
JOIN payment AS p
ON c.customer_id = p.customer_id;
AS is optional:
FROM customer c
JOIN payment p
Both are valid.
Aliases make JOIN queries:
An INNER JOIN returns only records that have a matching record in both tables.
Think:
Table A Table B
○────────────○
Matching
records
Find customers and their payments:
SELECT
c.customer_id,
c.first_name,
c.last_name,
p.amount
FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id;
PostgreSQL finds:
customer.customer_id
=
payment.customer_id
and combines matching rows.
JOIN
and
INNER JOIN
normally mean the same thing.
So this:
FROM customer
JOIN payment
ON customer.customer_id = payment.customer_id
is equivalent to:
FROM customer
INNER JOIN payment
ON customer.customer_id = payment.customer_id
Show the first name, last name, and payment amount of customers.
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
INNER JOIN payment p
ON c.customer_id = p.customer_id;
Find payments greater than 5:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
INNER JOIN payment p
ON c.customer_id = p.customer_id
WHERE p.amount > 5;
This demonstrates that JOIN and WHERE can be used together.
JOIN is not limited to two tables.
Suppose we want:
Customer name + rental date + film title
We need:
customer
↓
rental
↓
inventory
↓
film
Query:
SELECT
c.first_name,
c.last_name,
r.rental_date,
f.title
FROM customer c
JOIN rental r
ON c.customer_id = r.customer_id
JOIN inventory i
ON r.inventory_id = i.inventory_id
JOIN film f
ON i.film_id = f.film_id;
This is a very useful real-world example.
A LEFT JOIN returns:
All records from the left table + matching records from the right table.
If there is no match, PostgreSQL returns NULL for the right table’s columns.
Think:
LEFT TABLE RIGHT TABLE
████████████████
████████████████
████████████████
+
matching
SELECT
c.customer_id,
c.first_name,
p.payment_id,
p.amount
FROM customer c
LEFT JOIN payment p
ON c.customer_id = p.customer_id;
Show all customers, including customers who do not have a matching payment.
If a customer has no payment:
| customer_id | first_name | payment_id | amount |
|---|---|---|---|
| 101 | John | NULL | NULL |
The customer is still shown because customer is the left table.
Suppose the question is:
Show all customers, including customers who have never made a payment.
Use:
SELECT
c.customer_id,
c.first_name,
c.last_name,
p.payment_id
FROM customer c
LEFT JOIN payment p
ON c.customer_id = p.customer_id;
The LEFT JOIN keeps every customer.
A very useful technique:
SELECT
c.customer_id,
c.first_name,
c.last_name
FROM customer c
LEFT JOIN payment p
ON c.customer_id = p.customer_id
WHERE p.payment_id IS NULL;
Meaning:
Find customers who have no payment record.
This pattern is important:
LEFT JOIN ...
WHERE right_table.id IS NULL
It is commonly used to find records without a matching record.
A RIGHT JOIN returns:
All records from the right table + matching records from the left table.
Example:
SELECT
c.first_name,
c.last_name,
p.payment_id,
p.amount
FROM customer c
RIGHT JOIN payment p
ON c.customer_id = p.customer_id;
The important table here is the right table, payment.
FROM customer c
LEFT JOIN payment p
Keeps all customers.
FROM customer c
RIGHT JOIN payment p
Keeps all payments.
LEFT JOIN → keep everything from the left table.
RIGHT JOIN → keep everything from the right table.
In practice, many developers prefer LEFT JOIN because it is often easier to read. A RIGHT JOIN can usually be rewritten as a LEFT JOIN by reversing the table order.
A FULL OUTER JOIN returns:
Conceptually:
LEFT TABLE RIGHT TABLE
████████████████████████████
████████████████████████████
████████████████████████████
ALL RECORDS
Syntax:
SELECT ...
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
For demonstration:
SELECT
c.customer_id,
c.first_name,
p.payment_id,
p.amount
FROM customer c
FULL OUTER JOIN payment p
ON c.customer_id = p.customer_id;
This returns all rows from both tables.
In the standard DVD Rental database, customers and payments are related through customer_id, so most/all payment records normally have a matching customer. Therefore, this example is useful for learning the syntax, but it may not visibly demonstrate unmatched rows.
A Self Join is a join in which a table is joined with itself.
We use a Self Join when we want to compare or connect rows within the same table.
Self Join = Joining a table with itself.
Consider an employee table:
employee
----------------
employee_id
employee_name
manager_id
Example data:
| employee_id | employee_name | manager_id |
|---|---|---|
| 1 | Ali | NULL |
| 2 | Ahmed | 1 |
| 3 | Sara | 1 |
| 4 | Bilal | 2 |
| 5 | Ayesha | 2 |
Here:
employee_id identifies an employee.employee_name stores the employee’s name.manager_id stores the employee_id of that employee’s manager.NULL means the employee does not have a manager.For example:
Ahmed → manager_id = 1
Employee ID 1 belongs to Ali, so Ali is Ahmed’s manager.
Both the employee and the manager are stored in the same table.
We need to connect:
Employee's manager_id
↓
Manager's employee_id
Therefore, we join the employee table with itself.
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employee AS e
JOIN employee AS m
ON e.manager_id = m.employee_id;
| employee | manager |
|---|---|
| Ahmed | Ali |
| Sara | Ali |
| Bilal | Ahmed |
| Ayesha | Ahmed |
employee AS e
e represents the employee.
employee AS m
m represents the manager.
Although both e and m come from the same table, we give them different aliases so that we can distinguish them.
employee table
↓
┌─────────────┐
│ e │ → Employee
└─────────────┘
JOIN
┌─────────────┐
│ m │ → Manager
└─────────────┘
ON e.manager_id = m.employee_id
This means:
Find the employee whose
manager_idmatches the manager’semployee_id.
Consider Ahmed:
| employee_id | employee_name | manager_id |
|---|---|---|
| 2 | Ahmed | 1 |
Ahmed’s manager_id is 1.
Now look for:
employee_id = 1
The table contains:
| employee_id | employee_name |
|---|---|
| 1 | Ali |
Therefore:
Ahmed → Ali
Employee → Manager
We use aliases because the same table appears twice.
employee AS e
employee AS m
Here:
e = employeem = managerTherefore:
e.employee_name
means the employee’s name.
And:
m.employee_name
means the manager’s name.
We can also display the IDs:
SELECT
e.employee_id,
e.employee_name AS employee,
m.employee_id AS manager_id,
m.employee_name AS manager
FROM employee AS e
JOIN employee AS m
ON e.manager_id = m.employee_id;
| employee_id | employee | manager_id | manager |
|---|---|---|---|
| 2 | Ahmed | 1 | Ali |
| 3 | Sara | 1 | Ali |
| 4 | Bilal | 2 | Ahmed |
| 5 | Ayesha | 2 | Ahmed |
The previous query uses:
JOIN
which means INNER JOIN.
It does not display employees who do not have a manager.
If we want to display all employees, including the top-level manager, use LEFT JOIN.
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employee AS e
LEFT JOIN employee AS m
ON e.manager_id = m.employee_id;
| employee | manager |
|---|---|
| Ali | NULL |
| Ahmed | Ali |
| Sara | Ali |
| Bilal | Ahmed |
| Ayesha | Ahmed |
Ali appears because LEFT JOIN keeps all employees.
The most important part of this Self Join is:
ON e.manager_id = m.employee_id
Think of it as:
Employee's manager_id
↓
=
↓
Manager's employee_id
Self Join is useful when rows in the same table have a relationship with other rows in that same table.
Common examples include:
Use the following PostgreSQL query to create the employee table for the Self Join example:
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
manager_id INT
);
INSERT INTO employee (employee_id, employee_name, manager_id)
VALUES
(1, 'Ali', NULL),
(2, 'Ahmed', 1),
(3, 'Sara', 1),
(4, 'Bilal', 2),
(5, 'Ayesha', 2);
SELECT * FROM employee;
| employee_id | employee_name | manager_id |
|---|---|---|
| 1 | Ali | NULL |
| 2 | Ahmed | 1 |
| 3 | Sara | 1 |
| 4 | Bilal | 2 |
| 5 | Ayesha | 2 |
Now you can practice the Self Join:
SELECT
e.employee_name AS employee,
m.employee_name AS manager
FROM employee AS e
LEFT JOIN employee AS m
ON e.manager_id = m.employee_id;
A CROSS JOIN produces every possible combination of rows from two tables.
Suppose:
Table A = 3 rows
Table B = 4 rows
CROSS JOIN produces:
3 × 4 = 12 rows
SELECT *
FROM table1
CROSS JOIN table2;
SELECT
c.first_name,
c.last_name,
s.first_name AS staff_first_name
FROM customer c
CROSS JOIN staff s;
This produces every customer–staff combination.
If there are 599 customers and 2 staff members:
599 × 2 = 1198 rows
A CROSS JOIN does not require an ON condition.
CROSS JOIN is useful when we intentionally need all possible combinations.
For example:
Products × Sizes
could produce:
Shirt + Small
Shirt + Medium
Shirt + Large
Shoes + Small
Shoes + Medium
Shoes + Large
But be careful:
CROSS JOIN can produce a very large number of rows.
A NATURAL JOIN automatically joins tables using columns that have the same name.
Syntax:
SELECT *
FROM table1
NATURAL JOIN table2;
PostgreSQL automatically looks for columns with matching names.
customer and payment both have:
customer_id
So we could write:
SELECT
first_name,
last_name,
amount
FROM customer
NATURAL JOIN payment;
PostgreSQL automatically uses the common column:
customer_id
NATURAL JOIN can be convenient, but it is generally less explicit.
Suppose today two tables have:
customer_id
as the only common column.
Later, a new column with the same name is added to both tables.
The behavior of the NATURAL JOIN could change automatically.
Therefore, beginners should generally prefer:
JOIN ...
ON ...
because it clearly tells us which columns are being used for the relationship.
For example:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id;
This is much easier to understand.
JOIN and WHERE have different jobs.
Connects related tables.
ON c.customer_id = p.customer_id
Filters the result.
WHERE p.amount > 5
Example:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
WHERE p.amount > 5;
Think:
JOIN → Connect tables
WHERE → Filter rows
We can also sort the result.
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
ORDER BY p.amount DESC;
This shows the highest payment amounts first.
We can calculate the total payment made by each customer.
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(p.amount) AS total_payment
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name
ORDER BY total_payment DESC;
This is a very practical example of JOIN + aggregate functions.
Find customers whose total payments are greater than 100:
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(p.amount) AS total_payment
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING SUM(p.amount) > 100;
Remember:
JOIN → connect tables
WHERE → filter individual rows
GROUP BY → create groups
HAVING → filter groups
Show the customer name, film title, and rental date.
We need four tables:
customer
↓
rental
↓
inventory
↓
film
Query:
SELECT
c.first_name,
c.last_name,
f.title,
r.rental_date
FROM customer c
JOIN rental r
ON c.customer_id = r.customer_id
JOIN inventory i
ON r.inventory_id = i.inventory_id
JOIN film f
ON i.film_id = f.film_id;
This is one of the best examples for understanding why JOINs are important.
Suppose we want:
Customer name, film title, payment amount.
We can use:
SELECT
c.first_name,
c.last_name,
f.title,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
JOIN rental r
ON p.rental_id = r.rental_id
JOIN inventory i
ON r.inventory_id = i.inventory_id
JOIN film f
ON i.film_id = f.film_id;
Conceptually:
customer
│
│ customer_id
↓
payment
│
│ rental_id
↓
rental
│
│ inventory_id
↓
inventory
│
│ film_id
↓
film
| JOIN | What does it return? |
|---|---|
INNER JOIN |
Matching records from both tables |
LEFT JOIN |
All left records + matching right records |
RIGHT JOIN |
All right records + matching left records |
FULL OUTER JOIN |
All records from both tables |
CROSS JOIN |
Every possible combination |
SELF JOIN |
A table joined with itself |
NATURAL JOIN |
Automatically joins columns with the same names |
Table A Table B
AAAAA
BBBBB
↑
MATCH
Only matching records.
Table A Table B
AAAAAAAAA
BBB
All A + matching B.
Table A Table B
AAA
BBBBBBBBB
Matching A + all B.
Table A Table B
AAAAAAAAA BBBBBBBBB
ALL
Everything from both tables.
A × B
A1 → B1
A1 → B2
A2 → B1
A2 → B2
...
Every combination.
Students should first master this pattern:
SELECT
table1.column,
table2.column
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
For DVD Rental:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id;
Then add filtering:
WHERE p.amount > 5;
Then sorting:
ORDER BY p.amount DESC;
Complete query:
SELECT
c.first_name,
c.last_name,
p.amount
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
WHERE p.amount > 5
ORDER BY p.amount DESC;
JOIN payment p
ON c.customer_id = p.customer_id
ON specifies the relationshipON table1.id = table2.id
WHERE filters recordsWHERE p.amount > 5
FROM customer c
LEFT JOIN payment p
FROM customer c
RIGHT JOIN payment p
INNER JOIN
CROSS JOIN
staff s1
JOIN staff s2
NATURAL JOIN
For beginner and professional SQL, this is usually clearer:
JOIN payment p
ON c.customer_id = p.customer_id
rather than relying on:
NATURAL JOIN
Try these without looking at the answers first.
Display customer first name, last name, and payment amount.
Display customer names and payments greater than 5.
Display all customers and their payments, including customers with no payment.
Find customers who have no payment records.
Display customer name, rental date, and film title.
Display film titles and their rental rates.
Display each customer’s total payment.
Display customers whose total payment is greater than 100.
Display all possible customer and staff combinations using CROSS JOIN.
Use a SELF JOIN on the staff table to find pairs of staff members working at the same store.
Use NATURAL JOIN to join customer and payment.
Rewrite the NATURAL JOIN query using an explicit JOIN ... ON condition.
-- INNER JOIN
SELECT *
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id;
-- LEFT JOIN
SELECT *
FROM customer c
LEFT JOIN payment p
ON c.customer_id = p.customer_id;
-- RIGHT JOIN
SELECT *
FROM customer c
RIGHT JOIN payment p
ON c.customer_id = p.customer_id;
-- FULL OUTER JOIN
SELECT *
FROM customer c
FULL OUTER JOIN payment p
ON c.customer_id = p.customer_id;
-- CROSS JOIN
SELECT *
FROM customer c
CROSS JOIN staff s;
-- SELF JOIN
SELECT *
FROM staff s1
JOIN staff s2
ON s1.store_id = s2.store_id;
-- NATURAL JOIN
SELECT *
FROM customer
NATURAL JOIN payment;
**INNER = matching LEFT = all left RIGHT = all right FULL = everything CROSS = combinations SELF = same table NATURAL = same-named columns**