Intermediate

JOINs

Learn to combine data from multiple tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and self-joins.

Why JOINs Matter

Real-world data is spread across multiple tables. JOINs let you combine related data by matching rows based on common columns (keys). This is the foundation of relational database design.

INNER JOIN

Returns only rows that have matching values in both tables.

SQL

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

-- Only returns orders where the user exists,
-- and users who have placed orders.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right. Unmatched right-side rows appear as NULL.

SQL

-- All users, even those without orders
SELECT u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Find users who have never ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

✅

Data science tip: LEFT JOIN + IS NULL is a common pattern to find missing relationships — customers without purchases, products without reviews, users without logins.

RIGHT JOIN and FULL OUTER JOIN

SQL

-- RIGHT JOIN: all orders, even if user was deleted
SELECT u.name, o.id, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN: all rows from both tables
SELECT u.name, o.id, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

JOIN Types Summary

JOIN Type	Left Table	Right Table	Use Case
INNER	Matching only	Matching only	Get related data from both tables
LEFT	All rows	Matching only	Keep all left rows, add right data
RIGHT	Matching only	All rows	Keep all right rows, add left data
FULL OUTER	All rows	All rows	See everything, find mismatches
CROSS	All rows	All rows	Cartesian product (every combination)

Multi-Table JOINs

SQL

-- Join three tables: orders + users + products
SELECT u.name,
       p.product_name,
       oi.quantity,
       oi.quantity * p.price AS line_total
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY u.name, p.product_name;

Self-Joins

A table joined to itself. Useful for hierarchical data or comparing rows within the same table.

SQL

-- Find employees and their managers
SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

⚠

Watch for duplicates. JOINs can multiply rows if there are multiple matches. Always check row counts before and after a JOIN to ensure you get the expected results.

← Previous SELECT & Filtering Next → Aggregation