Home/Learn/MySQL/JOINs — INNER, LEFT, RIGHT, CROSS

JOINs — INNER, LEFT, RIGHT, CROSS

Beginner

Joins & Set Ops

INNER JOIN returns matching rows; LEFT/RIGHT JOIN includes unmatched rows from one side; CROSS JOIN produces a Cartesian product; choose the right join to avoid silent data exclusion.

Overview

JOINs combine rows from two or more tables based on a related column. Choosing the wrong join type is one of the most common bugs in SQL — an INNER JOIN silently drops rows with no match, which may look correct on a small dataset but produce wrong results in production when some records have NULL foreign keys or missing parent rows. MySQL supports INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, CROSS JOIN, and SELF JOIN. FULL OUTER JOIN is not natively supported in MySQL but can be emulated with UNION. The ON clause specifies the join condition, and you should always have an index on the join column for performance.

INNER JOIN vs LEFT JOIN

INNER JOIN returns only rows where the join condition is satisfied in both tables. If a customer has no orders, that customer does not appear in the result.

LEFT JOIN returns all rows from the left table plus matching rows from the right. If there is no match, right-side columns are NULL. Use LEFT JOIN when the right side is optional (e.g., show all customers including those with no orders).

RIGHT JOIN is the mirror of LEFT JOIN — it is rarely used because you can always rewrite it as a LEFT JOIN by swapping table order.

SQL — MySQL

-- Schema
-- customers: id, name, city
-- orders:    id, customer_id, total, created_at

-- INNER JOIN: only customers who have at least one order
SELECT c.name, o.id AS order_id, o.total
FROM   customers c
INNER  JOIN orders o ON c.id = o.customer_id;
-- Customers with no orders are excluded

-- LEFT JOIN: all customers, NULL for orders if none exist
SELECT c.name,
       COUNT(o.id)     AS order_count,
       SUM(o.total)    AS total_spent
FROM   customers c
LEFT   JOIN orders o ON c.id = o.customer_id
GROUP  BY c.id, c.name;
-- Returns every customer, 0 count and NULL sum for those with no orders

Self JOIN & Multi-Table JOIN

A SELF JOIN joins a table to itself using table aliases. Useful for hierarchical data like employee-manager relationships or finding pairs of rows with a relationship.

You can JOIN more than two tables by chaining JOIN clauses. MySQL optimises the join order internally, but adding indexes on all JOIN columns is essential for performance.

SQL — MySQL

-- Self JOIN: find each employee and their manager's name
SELECT e.name  AS employee,
       m.name  AS manager
FROM   employees e
LEFT   JOIN employees m ON e.manager_id = m.id;
-- LEFT JOIN ensures employees without a manager (CEO) still appear

-- Multi-table JOIN: orders with customer and product info
SELECT c.name       AS customer,
       o.id         AS order_id,
       p.name       AS product,
       oi.quantity
FROM   orders        o
JOIN   customers     c  ON o.customer_id = c.id
JOIN   order_items   oi ON oi.order_id   = o.id
JOIN   products      p  ON oi.product_id = p.id
WHERE  o.created_at >= '2025-01-01'
ORDER  BY o.id;

CROSS JOIN & Performance Tips

CROSS JOIN produces the Cartesian product — every row in table A paired with every row in table B. Rarely used intentionally; often the cause of accidentally huge result sets when a JOIN condition is missing.

Performance: Always index the join columns (usually the FK column). Check EXPLAIN output for join type — "eq_ref" is ideal (unique index lookup per row), "ref" is good (index scan), "ALL" means a full table scan and should be avoided.

SQL — MySQL

-- CROSS JOIN example (intentional — generate a date series for a report)
SELECT d.date_value, COALESCE(COUNT(o.id), 0) AS order_count
FROM  (SELECT DATE_ADD('2025-01-01', INTERVAL n DAY) AS date_value
       FROM   (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
               UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) nums) d
LEFT  JOIN orders o ON DATE(o.created_at) = d.date_value
GROUP BY d.date_value;

-- EXPLAIN: check join type for slow queries
EXPLAIN
SELECT c.name, o.total
FROM   customers c
JOIN   orders o ON c.id = o.customer_id
WHERE  c.city = 'Mumbai';
-- Look for: type = 'ref' or 'eq_ref' (good), type = 'ALL' (add an index!)

Key Points to Remember

1INNER JOIN returns only matched rows from both sides — it silently excludes rows with no match, which can lead to incorrect reports.
2LEFT JOIN returns all rows from the left table; use it when the right side is optional (e.g., customers with or without orders).
3MySQL does not support FULL OUTER JOIN natively; emulate it with LEFT JOIN UNION RIGHT JOIN.
4Always index the columns used in ON clauses — especially FK columns on the child table — to avoid full table scans.
5A missing ON condition in a JOIN produces a CROSS JOIN (Cartesian product) — every row times every row.
6Use EXPLAIN to verify that joins use indexes (type = ref/eq_ref) rather than full table scans (type = ALL).

Interview Questions

What is the difference between INNER JOIN and LEFT JOIN? Give a practical example.

EasyAmazon

You have a query that returns fewer rows than expected. What join-related issue might be the cause?

MediumInfosys

How do you find all customers who have never placed an order using SQL?

EasyTCS

What is a self join and when would you use it?

MediumGoogle

How does MySQL optimise multi-table JOINs internally? How can you influence this?

HardUber

Ask Aria about JOINs — INNER, LEFT, RIGHT, CROSS

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

SELECT Queries & Filtering

Subqueries