When working with PostgreSQL, data is often stored across multiple tables.
For example, an e-commerce application might have separate tables for:
users
orders
products
payments
But what if you want to retrieve information from multiple tables in a single query?
That’s where JOINs come in.
A PostgreSQL JOIN combines rows from two or more tables based on a related column or condition.
JOINs are one of the most important SQL concepts for backend developers because real-world databases rarely keep all information in one table.
In this guide, we’ll learn PostgreSQL JOINs from the basics with practical examples.
We’ll cover:
- What is a JOIN?
- Why JOINs are needed
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- CROSS JOIN
- SELF JOIN
- Multiple JOINs
- JOIN with WHERE
- JOIN with GROUP BY
- JOIN with ORDER BY
- JOIN vs subquery
- JOIN performance
- Indexes and JOINs
- Common JOIN mistakes
- Best practices
What Is a JOIN?
A JOIN allows you to combine data from different tables.
Suppose we have two tables.
Users
| id | name |
|---|---|
| 1 | Oliver |
| 2 | Sophia |
| 3 | Ethan |
Orders
| id | user_id | total |
|---|---|---|
| 101 | 1 | 299.99 |
| 102 | 2 | 149.99 |
| 103 | 1 | 499.99 |
The relationship is:
users.id
↓
orders.user_id
We can use a JOIN to retrieve the user’s name along with their orders.
SELECT
users.name,
orders.total
FROM users
JOIN orders
ON users.id = orders.user_id;
Result:
| name | total |
|---|---|
| Oliver | 299.99 |
| Sophia | 149.99 |
| Oliver | 499.99 |
The JOIN connects the related rows.
Why Do We Need JOINs?
A well-designed relational database usually separates data into multiple tables.
Instead of storing everything in one huge table:
users + orders + products + payments
we separate them:
users
orders
products
payments
This reduces unnecessary duplication and makes the database easier to maintain.
JOINs allow us to bring related information back together when we need it.
Basic JOIN Syntax
The general syntax is:
SELECT columns
FROM table1
JOIN table2
ON table1.column = table2.column;
For example:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
Here:
u → users
o → orders
These are called table aliases.
Aliases make queries shorter and easier to read.
Types of PostgreSQL JOINs
PostgreSQL supports several JOIN types.
The most important ones are:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN
Let’s understand each one.
1. INNER JOIN
An INNER JOIN returns only rows where a matching record exists in both tables.
For example:
SELECT
u.name,
o.total
FROM users u
INNER JOIN orders o
ON u.id = o.user_id;
If every order belongs to a valid user, the result might be:
| name | total |
|---|---|
| Oliver | 299.99 |
| Sophia | 149.99 |
| Oliver | 499.99 |
Think of an INNER JOIN as:
Table A
∩
Table B
Only matching rows are returned.
INNER JOIN Example
Suppose the users table contains:
| id | name |
|---|---|
| 1 | Oliver |
| 2 | Sophia |
| 3 | Ethan |
And orders contains:
| id | user_id | total |
|---|---|---|
| 101 | 1 | 299.99 |
| 102 | 2 | 149.99 |
Ethan has no order.
With:
SELECT
u.name,
o.total
FROM users u
INNER JOIN orders o
ON u.id = o.user_id;
Ethan will not appear.
Result:
| name | total |
|---|---|
| Oliver | 299.99 |
| Sophia | 149.99 |
2. LEFT JOIN
A LEFT JOIN returns:
- All rows from the left table
- Matching rows from the right table
NULLwhen no matching row exists
Example:
SELECT
u.name,
o.total
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;
Result:
| name | total |
|---|---|
| Oliver | 299.99 |
| Sophia | 149.99 |
| Ethan | NULL |
Ethan appears even though he doesn’t have an order.
When Should You Use LEFT JOIN?
LEFT JOIN is useful when you want to keep all records from your main table.
For example:
Show all users and their orders, including users who haven’t placed an order.
SELECT
u.name,
o.id AS order_id,
o.total
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;
This is a very common application requirement.
Finding Records With No Match
LEFT JOIN can also be used to find records that don’t have related data.
For example:
Find users who have never placed an order.
SELECT
u.id,
u.name
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.id IS NULL;
Result:
Ethan
The pattern is:
LEFT JOIN
↓
WHERE right_table.id IS NULL
This is commonly called an anti-join pattern.
3. RIGHT JOIN
A RIGHT JOIN is essentially the opposite orientation of a LEFT JOIN.
It returns:
- All rows from the right table
- Matching rows from the left table
NULLfor missing matches
Example:
SELECT
u.name,
o.total
FROM users u
RIGHT JOIN orders o
ON u.id = o.user_id;
This keeps every row from orders.
In practice, many developers prefer rewriting RIGHT JOINs as LEFT JOINs by changing the table order because LEFT JOIN is often easier to read.
4. FULL OUTER JOIN
A FULL OUTER JOIN returns all rows from both tables.
Matching rows are combined.
Rows without a match contain NULL values for the other table.
Example:
SELECT
u.name,
o.total
FROM users u
FULL OUTER JOIN orders o
ON u.id = o.user_id;
Conceptually:
Users only
+
Matching rows
+
Orders only
FULL OUTER JOIN Example
Suppose users contains:
| id | name |
|---|---|
| 1 | Oliver |
| 2 | Sophia |
And orders contains:
| id | user_id | total |
|---|---|---|
| 101 | 1 | 299.99 |
| 102 | 99 | 149.99 |
There is no user with ID 99.
A FULL OUTER JOIN can return both unmatched records.
| name | total |
|---|---|
| Oliver | 299.99 |
| Sophia | NULL |
| NULL | 149.99 |
This can be useful when you want to identify unmatched data on both sides.
5. CROSS JOIN
A CROSS JOIN produces the Cartesian product of two tables.
Suppose:
Colors
| color |
|---|
| Black |
| White |
Sizes
| size |
|---|
| Small |
| Large |
Query:
SELECT
c.color,
s.size
FROM colors c
CROSS JOIN sizes s;
Result:
| color | size |
|---|---|
| Black | Small |
| Black | Large |
| White | Small |
| White | Large |
If one table has 2 rows and another has 2 rows:
2 × 2 = 4 rows
Be careful with CROSS JOIN because large tables can produce enormous result sets.
6. SELF JOIN
A SELF JOIN joins a table to itself.
This is useful for hierarchical relationships.
For example, an employees table might contain:
| id | name | manager_id |
|---|---|---|
| 1 | Oliver | NULL |
| 2 | Sophia | 1 |
| 3 | Ethan | 1 |
Here:
Oliver
├── Sophia
└── Ethan
We can join the table to itself:
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;
Result:
| employee | manager |
|---|---|
| Oliver | NULL |
| Sophia | Oliver |
| Ethan | Oliver |
This is called a SELF JOIN.
JOIN with Table Aliases
Aliases make complex queries much easier to read.
Instead of:
SELECT
users.name,
orders.total
FROM users
JOIN orders
ON users.id = orders.user_id;
you can write:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
This becomes especially useful when multiple tables have columns with the same name.
Joining Multiple Tables
Real applications often require more than two tables.
Suppose we have:
users
orders
order_items
products
The relationship could be:
users
↓
orders
↓
order_items
↓
products
We can join all of them:
SELECT
u.name,
o.id AS order_id,
p.name AS product,
oi.quantity
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN order_items oi
ON o.id = oi.order_id
JOIN products p
ON oi.product_id = p.id;
This allows us to retrieve information from four tables in one query.
JOIN with WHERE
You can filter joined results using WHERE.
For example:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id
WHERE o.total > 500;
This returns only orders greater than 500.
JOIN with ORDER BY
You can also sort the results.
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id
ORDER BY o.total DESC;
This returns the highest-value orders first.
JOIN with GROUP BY
JOINs are frequently combined with aggregation.
For example:
Find the total amount spent by each user.
SELECT
u.name,
SUM(o.total) AS total_spent
FROM users u
JOIN orders o
ON u.id = o.user_id
GROUP BY u.id, u.name;
Result:
| name | total_spent |
|---|---|
| Oliver | 799.98 |
| Sophia | 149.99 |
This is a common reporting query.
LEFT JOIN with GROUP BY
Suppose you want to show every user, including users who have no orders.
Use:
SELECT
u.name,
COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
GROUP BY u.id, u.name;
COALESCE() converts NULL into 0.
Result:
| name | total_spent |
|---|---|
| Oliver | 799.98 |
| Sophia | 149.99 |
| Ethan | 0 |
JOIN Conditions
The ON clause defines how rows are related.
For example:
ON u.id = o.user_id
This means:
users.id
=
orders.user_id
You can also use more complex conditions.
For example:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id
AND o.status = 'completed';
Here the JOIN only matches completed orders.
JOIN Condition vs WHERE Condition
This difference becomes especially important with LEFT JOIN.
Consider:
SELECT
u.name,
o.total
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
AND o.status = 'completed';
This keeps all users but only joins completed orders.
Now compare:
SELECT
u.name,
o.total
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.status = 'completed';
The WHERE condition removes rows where o.status is NULL, which can effectively turn the LEFT JOIN behavior into an INNER JOIN for that condition.
Understanding this difference is very important when writing SQL.
JOIN with Multiple Conditions
You can use multiple conditions in the ON clause.
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id
AND o.status = 'completed'
AND o.total > 100;
The JOIN only matches orders satisfying all conditions.
Joining on More Than IDs
Although joins commonly use IDs:
ON users.id = orders.user_id
you can technically join on other columns.
For example:
SELECT
u.name,
c.name AS country
FROM users u
JOIN countries c
ON u.country_code = c.code;
However, the columns used for relationships should generally have appropriate constraints and indexes where necessary.
JOIN Using USING
PostgreSQL supports the USING syntax when both tables have a column with the same name.
For example:
SELECT *
FROM users
JOIN orders
USING (user_id);
This requires both tables to contain a column called user_id.
When appropriate, USING can make queries shorter.
NATURAL JOIN
PostgreSQL also supports NATURAL JOIN.
For example:
SELECT *
FROM users
NATURAL JOIN orders;
It automatically joins tables using columns with matching names.
However, NATURAL JOIN is generally not recommended in application code because changing table schemas can unexpectedly change the join behavior.
Explicit JOIN conditions are clearer and safer.
JOIN vs Subquery
Sometimes you can solve the same problem with either a JOIN or a subquery.
For example, using JOIN:
SELECT
u.name
FROM users u
JOIN orders o
ON u.id = o.user_id
WHERE o.total > 500;
A subquery could also be used:
SELECT name
FROM users
WHERE id IN (
SELECT user_id
FROM orders
WHERE total > 500
);
Which is better?
It depends on the query.
Modern PostgreSQL can optimize many equivalent query forms effectively.
Choose the version that clearly represents the intended logic, then use EXPLAIN ANALYZE when performance matters.
JOIN Performance
JOINs can be extremely efficient when tables and queries are designed correctly.
Performance depends on factors such as:
- Table size
- Join conditions
- Indexes
- Data distribution
- Query structure
- Statistics
- Available memory
- PostgreSQL configuration
Don’t assume that using a JOIN automatically makes a query slow.
Indexes and JOINs
Indexes can be particularly useful on columns used for joins.
For example:
CREATE INDEX idx_orders_user_id
ON orders(user_id);
Now PostgreSQL has an index on:
orders.user_id
This can help queries joining:
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
The PostgreSQL planner decides whether using that index is actually beneficial.
Primary Keys and JOINs
Primary keys are automatically backed by unique indexes in PostgreSQL.
For example:
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
name TEXT
);
The id column has an index supporting the primary key.
This is useful when another table references it:
orders.user_id
↓
users.id
Remember that the referencing foreign-key column isn’t automatically indexed.
JOIN Algorithms in PostgreSQL
PostgreSQL can use different algorithms to execute joins.
Important ones include:
Nested Loop
Hash Join
Merge Join
Nested Loop
PostgreSQL repeatedly searches one relation for matching rows from another.
This can be efficient when one side is small or suitable indexes are available.
Hash Join
PostgreSQL builds a hash structure for one side and uses it to find matching rows.
This can be effective for many equality joins.
Merge Join
PostgreSQL processes sorted inputs and matches rows in order.
It can be useful when inputs are already sorted or can be efficiently sorted.
The planner chooses the strategy based on estimated costs.
Understanding JOIN Execution with EXPLAIN
You can inspect a query plan with:
EXPLAIN
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
For actual execution statistics:
EXPLAIN ANALYZE
SELECT
u.name,
o.total
FROM users u
JOIN orders o
ON u.id = o.user_id;
You may see:
Hash Join
or:
Nested Loop
or:
Merge Join
Understanding execution plans is an important PostgreSQL performance skill.
Common JOIN Mistakes
1. Forgetting the JOIN Condition
Be careful with:
SELECT *
FROM users
JOIN orders;
A JOIN without an appropriate condition can produce a Cartesian product or otherwise not express the intended relationship.
Use:
JOIN orders
ON users.id = orders.user_id
when that is the intended relationship.
2. Using INNER JOIN When LEFT JOIN Is Needed
If you want all users, including users without orders:
LEFT JOIN
is usually appropriate.
An INNER JOIN would remove users who don’t have matching orders.
3. Filtering a LEFT JOIN in WHERE
This pattern:
LEFT JOIN orders o
ON u.id = o.user_id
WHERE o.status = 'completed'
can remove users with no matching orders.
If you want to preserve those users, consider putting the condition in the ON clause:
LEFT JOIN orders o
ON u.id = o.user_id
AND o.status = 'completed'
4. Joining the Wrong Columns
Make sure your JOIN condition represents the actual relationship.
For example:
ON users.id = orders.user_id
is very different from:
ON users.id = orders.id
The second query might technically execute but produce incorrect results.
5. Selecting Duplicate Column Names
Suppose both tables contain:
id
created_at
Instead of:
SELECT id
use:
SELECT
u.id AS user_id,
o.id AS order_id
This makes the result much clearer.
6. Forgetting About Duplicate Rows
If one user has five orders:
Oliver
├── Order 1
├── Order 2
├── Order 3
├── Order 4
└── Order 5
a JOIN between users and orders can return five rows for Oliver.
That’s expected.
If you need one row per user, you may need aggregation or another query strategy.
JOINs and NULL Values
NULL values can affect JOIN results.
Suppose:
users.id = 101
orders.user_id = NULL
This doesn’t match:
ON users.id = orders.user_id
because comparisons involving NULL don’t evaluate to true using normal equality.
Understanding NULL behavior is important when debugging unexpected JOIN results.
JOINs with Three or More Tables
You can continue adding JOINs.
For example:
SELECT
u.name,
o.id AS order_id,
p.name AS product
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN order_items oi
ON o.id = oi.order_id
JOIN products p
ON oi.product_id = p.id;
Think of it as:
users
↓
orders
↓
order_items
↓
products
Each JOIN adds another relationship to the result.
Practical E-Commerce Example
Imagine the following tables.
Users
id
name
email
Orders
id
user_id
created_at
status
Order Items
id
order_id
product_id
quantity
Products
id
name
price
Now suppose we want to find:
Each user’s completed orders and the products included in those orders.
We can write:
SELECT
u.name AS customer,
o.id AS order_id,
p.name AS product,
oi.quantity
FROM users u
JOIN orders o
ON u.id = o.user_id
JOIN order_items oi
ON o.id = oi.order_id
JOIN products p
ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;
This is a realistic example of using multiple PostgreSQL JOINs.
JOIN Cheat Sheet
| JOIN Type | What It Returns |
|---|---|
| INNER JOIN | Matching rows from both tables |
| LEFT JOIN | All left rows + matching right rows |
| RIGHT JOIN | All right rows + matching left rows |
| FULL OUTER JOIN | All rows from both tables |
| CROSS JOIN | Every combination of rows |
| SELF JOIN | A table joined with itself |
A simple visual:
INNER JOIN
A ∩ B
LEFT JOIN
A + matching B
RIGHT JOIN
matching A + B
FULL JOIN
A + B
CROSS JOIN
A × B
PostgreSQL JOIN Best Practices
Use Explicit JOIN Conditions
Prefer:
JOIN orders
ON users.id = orders.user_id
over implicit comma joins.
Use Table Aliases
For example:
users u
orders o
This keeps complex queries readable.
Select Only Required Columns
Instead of:
SELECT *
prefer:
SELECT
u.name,
o.total
when you know which fields you need.
Index Important Join Columns
Especially consider frequently used foreign-key columns and other join keys.
Use EXPLAIN ANALYZE
Measure query performance instead of guessing.
Choose JOIN Type Carefully
Understand the difference between INNER JOIN and LEFT JOIN before using them.
Avoid NATURAL JOIN
Explicit relationships are safer and easier to understand.
Final Thoughts
JOINs are fundamental to PostgreSQL and relational database development.
The core idea is simple:
Table A
↓
Related Column
↓
Table B
↓
Combined Result
The most important JOINs to learn first are:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
Then learn:
CROSS JOIN
SELF JOIN
Multiple JOINs
JOIN + GROUP BY
JOIN + WHERE
JOIN + ORDER BY
For backend developers, understanding JOINs is essential because real applications usually store related information across multiple tables.
The most important thing isn’t just memorizing JOIN syntax.
You should understand what rows each JOIN keeps and what rows it removes.
For example:
INNER JOIN
→ Keep matches
LEFT JOIN
→ Keep everything from the left
FULL OUTER JOIN
→ Keep everything from both sides
Once you understand these concepts, complex PostgreSQL queries become much easier to write and debug.
Mastering JOINs means learning how to combine relational data accurately, efficiently, and predictably.
