MySQL Joins Explained — INNER, LEFT, RIGHT, CROSS & More (2026)

Introduction#

Why This Matters#

Relational databases store data across multiple tables to avoid redundancy — a design principle called normalisation. Joins are the mechanism that re-assembles that split data for queries. Every non-trivial SELECT statement in a real application involves at least one join. Misusing joins is one of the most common causes of slow queries, duplicate rows, and incorrect reports.

Understanding joins deeply — knowing which type returns what rows, how NULL values behave, and how indexes influence performance — is a skill that separates experienced SQL writers from beginners.

Key Relational Concepts#

Primary Key: A column (or set of columns) that uniquely identifies every row in a table. A table can have only one primary key.

Foreign Key: A column in one table whose values reference the primary key of another table. This is called referential integrity. A table can have multiple foreign keys.

Degree: The number of columns in a table.

Cardinality: The number of rows in a table.

Sample Tables#

All examples use these two tables:

-- student table (degree = 2, cardinality = 5)
CREATE TABLE student (
    admno INT PRIMARY KEY,
    name  VARCHAR(30)
);
 
-- student_class table (degree = 3, cardinality = 5)
CREATE TABLE student_class (
    admno   INT REFERENCES student(admno),
    class   VARCHAR(20),
    section CHAR(1)
);

Equi Join (Classic Syntax)#

An equi join combines rows from two tables where a column value matches. The classic syntax uses the WHERE clause:

SELECT name, class, section
FROM student, student_class
WHERE student.admno = student_class.admno;

Table aliases shorten the query:

SELECT a.admno, name, class, section
FROM student a, student_class b
WHERE a.admno = b.admno;

Add extra conditions with AND:

-- Students in the English class, section B
SELECT name, class, section
FROM student a, student_class b
WHERE a.admno = b.admno
  AND class = 'English'
  AND section = 'B';

INNER JOIN#

INNER JOIN is the modern, explicit syntax for an equi join. It returns only rows that have a matching value in both tables.

SELECT student.name, student_class.class, student_class.section
FROM student
INNER JOIN student_class ON student.admno = student_class.admno;

If a student exists in student but has no matching row in student_class, that student is excluded from the result.

LEFT JOIN (LEFT OUTER JOIN)#

LEFT JOIN returns all rows from the left table plus matched rows from the right table. Where no match exists, right-table columns contain NULL.

SELECT student.name, student_class.class, student_class.section
FROM student
LEFT JOIN student_class ON student.admno = student_class.admno;

Use LEFT JOIN to find students who have not been assigned a class:

SELECT student.admno, student.name
FROM student
LEFT JOIN student_class ON student.admno = student_class.admno
WHERE student_class.admno IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)#

RIGHT JOIN returns all rows from the right table plus matched rows from the left table. Where no match exists, left-table columns contain NULL.

SELECT student.name, student_class.class, student_class.section
FROM student
RIGHT JOIN student_class ON student.admno = student_class.admno;

FULL OUTER JOIN#

MySQL does not have a native FULL OUTER JOIN, but you can emulate it with UNION:

SELECT student.name, student_class.class, student_class.section
FROM student
LEFT JOIN student_class ON student.admno = student_class.admno
 
UNION
 
SELECT student.name, student_class.class, student_class.section
FROM student
RIGHT JOIN student_class ON student.admno = student_class.admno;

UNION automatically removes duplicate rows. Use UNION ALL to keep duplicates.

CROSS JOIN (Cartesian Product)#

CROSS JOIN combines every row of the first table with every row of the second table. With 5 rows in each table, the result has 5 × 5 = 25 rows.

SELECT student.name, student_class.class
FROM student
CROSS JOIN student_class;

The old-style equivalent (comma-separated tables with no WHERE) also produces a Cartesian product:

SELECT * FROM student, student_class;

Cartesian products are rarely intentional — always verify you have a WHERE or ON condition.

NATURAL JOIN#

NATURAL JOIN automatically joins on all columns with the same name in both tables. With these tables, it joins on admno:

SELECT name, class, section
FROM student
NATURAL JOIN student_class;

Avoid NATURAL JOIN in production code — adding a column with the same name to either table silently changes the join condition.

SELF JOIN#

A self join joins a table to itself using aliases. It is used to find hierarchical relationships or compare rows within the same table.

-- Pair every student with every other student
SELECT s1.name AS student_name, s2.name AS peer_name
FROM student s1
INNER JOIN student s2 ON s1.admno != s2.admno
ORDER BY s1.admno;

UNION and UNION ALL#

UNION combines the result sets of two SELECT statements with the same number of columns and compatible data types. Duplicates are removed by default.

SELECT name FROM student
UNION
SELECT class FROM student_class;

UNION ALL keeps all rows including duplicates and is faster because it skips the deduplication step.

Common Mistakes#

• Cartesian products from missing join conditions. Always include ON or WHERE when joining tables.
• Ambiguous column names. If both tables have a column named admno, reference it as student.admno or use aliases.
• Using NATURAL JOIN in production. A schema change (adding a column) can silently break the query.
• Ignoring NULL in OUTER JOINs. After a LEFT JOIN, columns from the right table may be NULL; check with IS NULL.
• Not indexing join columns. The optimizer uses indexes on join columns; missing indexes cause full table scans.

Best Practices#

• Prefer the explicit JOIN ... ON syntax over the implicit comma syntax for clarity and maintainability.
• Always qualify ambiguous column names with the table name or alias.
• Index every foreign key column — MySQL does not create these automatically.
• Use EXPLAIN SELECT ... to verify that the optimizer is using indexes on your join columns.
• Use INNER JOIN by default; switch to LEFT JOIN only when you explicitly need rows with no matching right-table record.
• Limit the columns in SELECT to only what you need, especially in joins — SELECT * across wide tables is expensive.

Key Takeaways#

• INNER JOIN returns only rows with a matching value in both tables; unmatched rows from either table are excluded.
• LEFT JOIN returns all rows from the left table; unmatched rows in the right table appear as NULL.
• RIGHT JOIN returns all rows from the right table; unmatched rows in the left table appear as NULL.
• CROSS JOIN produces a Cartesian product (m × n rows) — every row from table A paired with every row from table B.
• NATURAL JOIN auto-joins on columns sharing the same name; avoid it in production due to fragility.
• A self join uses table aliases to join a table with itself, enabling hierarchical or intra-table comparisons.
• UNION merges two result sets and removes duplicates; UNION ALL keeps all rows and is faster.
• Indexing foreign key and join columns is critical for performance — missing indexes cause full table scans.