PostgreSQL JOINS

PostgreSQL JOINS

JOINS in PostgreSQL are used to combine rows from two or more tables based on a related column (usually a primary–foreign key relationship).

1️⃣ Why Use JOINS?

Example tables:

users

id name
1 Amit
2 Riya

orders

id user_id amount
101 1 500
102 1 1200
103 2 700

To get user name with order amount, we use JOINs.

2️⃣ INNER JOIN

Returns only matching rows from both tables.

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o
ON u.id = o.user_id;

✔ Most commonly used JOIN.

3️⃣ LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from left table and matching rows from right table.
If no match → NULL.

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;

✔ Shows users even if they have no orders.

4️⃣ RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from right table.

SELECT u.name, o.amount
FROM users u
RIGHT JOIN orders o
ON u.id = o.user_id;

✔ Less common (can usually be rewritten as LEFT JOIN).

5️⃣ FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables.

SELECT u.name, o.amount
FROM users u
FULL JOIN orders o
ON u.id = o.user_id;

✔ Includes unmatched rows from both sides.

6️⃣ CROSS JOIN

Returns Cartesian product (every row with every row).

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

❗ Use carefully — result size can explode.

7️⃣ SELF JOIN

A table joined with itself.

Example: employees with managers.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

8️⃣ JOIN with WHERE vs ON

-- Correct
SELECT *
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id
WHERE u.status = 'active';

❗ Filtering JOIN condition in WHERE can change results.

9️⃣ JOIN Using USING Clause

When column names are same.

SELECT name, amount
FROM users
JOIN orders USING (id);

🔟 JOIN Multiple Tables

SELECT u.name, o.amount, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

1️⃣1️⃣ Visual Summary

JOIN Type Result
INNER Matching rows only
LEFT All left + matching right
RIGHT All right + matching left
FULL All rows from both
CROSS Every combination
SELF Table joined with itself

1️⃣2️⃣ Common JOIN Errors

❌ Missing ON condition
❌ Joining wrong columns
❌ Unexpected NULL values
❌ Huge result due to CROSS JOIN

1️⃣3️⃣ Best Practices

✔ Always specify JOIN condition
✔ Use table aliases
✔ Prefer LEFT JOIN over RIGHT JOIN
✔ Index foreign key columns
✔ Test with small datasets first

You may also like...