SQL JOIN Types
JOINs combine rows from two or more tables based on related columns. They are the most important SQL concept after SELECT — relational databases store data across multiple tables, and JOINs bring them together.
45 min•By Priygop Team•Last updated: Feb 2026
JOIN Types Explained
- INNER JOIN — Returns only matching rows from both tables. Most common join type. No match = row excluded
- LEFT JOIN — Returns ALL rows from left table + matching rows from right. No match = NULL for right columns
- RIGHT JOIN — Returns ALL rows from right table + matching rows from left. Less common — rewrite as LEFT JOIN
- FULL OUTER JOIN — Returns ALL rows from both tables. No match on either side = NULL. Not supported in MySQL
- CROSS JOIN — Returns every combination (cartesian product). 10 rows × 10 rows = 100 rows. Use sparingly
- Self JOIN — Table joined to itself. Used for hierarchical data (employees → managers) or comparisons within same table
- ON clause — JOIN condition: ON users.id = orders.user_id. Always join on indexed foreign key columns
JOIN Query Examples
Example
-- INNER JOIN: Get orders with customer names
SELECT
o.id AS order_id,
c.name AS customer,
o.total,
o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.total DESC;
-- LEFT JOIN: All customers, even those without orders
SELECT
c.name,
c.email,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email;
-- Customers with 0 orders still appear (order columns are NULL)
-- Multiple JOINs: Orders → Customers → Products
SELECT
o.id AS order_id,
c.name AS customer,
p.name AS product,
oi.quantity,
oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2025-01-01';
-- Self JOIN: 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;
-- Find customers WITHOUT orders (anti-join)
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;
-- This is more efficient than WHERE NOT IN (subquery)