Joins

A join combines rows from two tables that match a condition. The join type decides what happens to rows with no match.

Join	Keeps
`INNER JOIN`	only rows that match in both tables
`LEFT JOIN`	all left rows; right columns are `NULL` when there is no match
`RIGHT JOIN`	all right rows; left columns are `NULL` when there is no match
`FULL JOIN`	all rows from both sides
`CROSS JOIN`	every left row paired with every right row

Example 1: INNER JOIN

Employees with their department name. Tara (no department) is dropped, and so is Legal (no employees).

SQL

SELECT e.name AS employee, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.dept_id
ORDER BY e.id;

Output

 employee | department  
----------+-------------
 Meera    | Engineering
 Ravi     | Engineering
 Kiran    | Engineering
 Neha     | Engineering
 Arjun    | Engineering
 Divya    | Sales
 Sameer   | Sales
 Pooja    | Sales
 Vikram   | Marketing
 Anita    | Marketing
 Rohan    | HR
(11 rows)

JOIN alone means INNER JOIN.

Example 2: LEFT JOIN keeps unmatched left rows

Every employee, with a department name when there is one.

SQL

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE e.id IN (11, 12)
ORDER BY e.id;

Output

 employee | department 
----------+------------
 Rohan    | HR
 Tara     | 
(2 rows)

Example 3: RIGHT JOIN

Every department, with employees when there are any. It is the same as swapping the tables in a LEFT JOIN, which most people prefer to write.

SQL

SELECT d.name AS department, e.name AS employee
FROM employees e
RIGHT JOIN departments d ON d.id = e.dept_id
WHERE d.id IN (4, 5)
ORDER BY d.id;

Output

 department | employee 
------------+----------
 HR         | Rohan
 Legal      | 
(2 rows)

Example 4: FULL JOIN

Rows unmatched on either side appear once, with NULL on the other side.

SQL

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL JOIN departments d ON d.id = e.dept_id
WHERE e.id IS NULL OR d.id IS NULL;

Output

 employee | department 
----------+------------
 Tara     | 
          | Legal
(2 rows)

Example 5: find rows with no match (anti-join)

Customers who never ordered. Left join, then keep the rows where the right side is NULL.

SQL

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Output

 name  
-------
 Nisha
(1 row)

NOT EXISTS says the same thing and is often clearer:

SQL

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Output

 name  
-------
 Nisha
(1 row)

Example 6: LEFT JOIN with a filter, ON vs WHERE

A condition in WHERE on the right table turns a left join into an inner join, because the NULL rows fail it.

SQL

SELECT c.name, count(o.id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.name
ORDER BY c.name;

Output

 name  | delivered_orders 
-------+------------------
 Aarav |                2
 Isha  |                1
 Karan |                2
(3 rows)

Customers with no delivered orders disappeared. Put the condition in ON to keep them:

SQL

SELECT c.name, count(o.id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered'
GROUP BY c.name
ORDER BY c.name;

Output

  name  | delivered_orders 
--------+------------------
 Aarav  |                2
 Isha   |                1
 Karan  |                2
 Lata   |                0
 Manish |                0
 Nisha  |                0
(6 rows)

Example 7: join three or more tables

Who bought what:

SQL

SELECT c.name AS customer, p.name AS product, oi.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE c.name = 'Karan'
ORDER BY o.id, p.name;

Output

 customer | product  | quantity 
----------+----------+----------
 Karan    | Laptop   |        1
 Karan    | Keyboard |        1
 Karan    | Webcam   |        2
(3 rows)

Example 8: self join

A table joined to itself, with two aliases. Each employee with their manager:

SQL

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
WHERE e.id <= 4
ORDER BY e.id;

Output

 employee | manager 
----------+---------
 Meera    | 
 Ravi     | Meera
 Kiran    | Ravi
 Neha     | Ravi
(4 rows)

Example 9: managers and their number of direct reports

SQL

SELECT m.name AS manager, count(*) AS reports
FROM employees e
JOIN employees m ON m.id = e.manager_id
GROUP BY m.name
ORDER BY reports DESC, m.name;

Output

 manager | reports 
---------+---------
 Meera   |       5
 Divya   |       2
 Ravi    |       2
 Kiran   |       1
 Vikram  |       1
(5 rows)

Example 10: pairs of rows from the same table

Two employees in the same department. The condition a.id < b.id lists each pair once and never pairs someone with themselves.

SQL

SELECT a.name AS first, b.name AS second
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id AND a.id < b.id
WHERE a.dept_id = 3;

Output

 first  | second 
--------+--------
 Vikram | Anita
(1 row)

Example 11: CROSS JOIN

Every combination. The result has rows_a x rows_b rows, so use it deliberately.

SQL

SELECT d.name AS department, s.status
FROM (SELECT name FROM departments WHERE id <= 2) d
CROSS JOIN (VALUES ('active'), ('inactive')) AS s(status)
ORDER BY d.name, s.status;

Output

 department  |  status  
-------------+----------
 Engineering | active
 Engineering | inactive
 Sales       | active
 Sales       | inactive
(4 rows)

Example 12: USING and NATURAL JOIN

USING (col) joins on a column with the same name in both tables and returns it once.

SQL

SELECT order_id, status, product_id, quantity
FROM (SELECT id AS order_id, status FROM orders) o
JOIN order_items USING (order_id)
WHERE order_id = 1;

Output

 order_id |  status   | product_id | quantity 
----------+-----------+------------+----------
        1 | delivered |          4 |        1
        1 | delivered |          2 |        2
(2 rows)

Avoid NATURAL JOIN. It joins on every column with a matching name, and silently changes meaning when someone adds a column.

Example 13: join on a range

A join condition can be any expression. Put each employee in a salary band:

SQL

SELECT e.name, e.salary, b.band
FROM employees e
JOIN (VALUES (0, 99999, 'low'), (100000, 179999, 'mid'), (180000, 999999, 'high')) AS b(lo, hi, band)
  ON e.salary BETWEEN b.lo AND b.hi
WHERE e.id <= 5
ORDER BY e.id;

Output

 name  | salary | band 
-------+--------+------
 Meera | 250000 | high
 Ravi  | 180000 | high
 Kiran | 150000 | mid
 Neha  | 150000 | mid
 Arjun | 110000 | mid
(5 rows)

Example 14: duplicates from a one-to-many join

Joining a customer to their orders returns one row per order.

SQL

SELECT c.name, o.id AS order_id
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1;

Output

 name  | order_id 
-------+----------
 Aarav |        1
 Aarav |        2
 Aarav |        6
(3 rows)

If you only want a fact about the customer, aggregate first or use EXISTS instead of joining.

Summary

Use LEFT JOIN when the left rows must survive.
A WHERE filter on the right table of a LEFT JOIN cancels the outer join. Put it in ON.
A one-to-many join repeats the "one" side. Watch out before count or sum.
LEFT JOIN ... WHERE right.id IS NULL and NOT EXISTS both find rows with no match.