MySQL JOIN Types – Complete Detailed Guide

What is a JOIN?

A JOIN combines rows from two or more tables based on a related column (usually a foreign key).

Syntax:
sql SELECT columns FROM table1 [JOIN TYPE] table2 ON table1.column = table2.column;

Sample Tables for Examples

-- Table 1: students
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT
);

-- Table 2: departments
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- Sample Data
INSERT INTO students VALUES 
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', NULL),
(4, 'Diana', 103);

INSERT INTO departments VALUES 
(101, 'Computer Science'),
(102, 'Mathematics'),
(104, 'Physics');

1. INNER JOIN (Most Common)

Returns only matching rows from both tables.

SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON s.dept_id = d.dept_id;

Result:
| name | dept_name |
|---------|------------------|
| Alice | Computer Science |
| Bob | Mathematics |

Note: Charlie (NULL) and Physics (no student) → excluded

2. LEFT JOIN (or LEFT OUTER JOIN)

Returns:
- All rows from LEFT table
- Matching rows from RIGHT table
- NULL in RIGHT if no match

SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

Result:
| name | dept_name |
|----------|------------------|
| Alice | Computer Science |
| Bob | Mathematics |
| Charlie | NULL |
| Diana | NULL |

Use Case: "Show all students, even if they have no department"

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns:
- All rows from RIGHT table
- Matching rows from LEFT table
- NULL in LEFT if no match

SELECT s.name, d.dept_name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;

Result:
| name | dept_name |
|-------|------------------|
| Alice | Computer Science |
| Bob | Mathematics |
| NULL | Physics |

Use Case: "Show all departments, even if no students enrolled"

4. FULL OUTER JOIN (Not supported in MySQL!)

Would return:
- All rows from both tables
- NULL where no match

MySQL does NOT support FULL OUTER JOIN

Simulate FULL OUTER JOIN in MySQL:

SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id

UNION

SELECT s.name, d.dept_name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id
WHERE s.id IS NULL;

Result:
| name | dept_name |
|----------|------------------|
| Alice | Computer Science |
| Bob | Mathematics |
| Charlie | NULL |
| Diana | NULL |
| NULL | Physics |

Pro Tip: Use UNION to simulate FULL JOIN

5. CROSS JOIN (Cartesian Product)

Returns all possible combinations of rows from both tables.

SELECT s.name, d.dept_name
FROM students s
CROSS JOIN departments d;

Result: 4 students × 3 depts = 12 rows

Use Case: Generating test data, combinations

6. SELF JOIN

Join a table to itself (e.g., employee → manager).

-- Example: employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee X', 2);

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

Result:
| employee | manager |
|-------------|----------|
| CEO | NULL |
| Manager A | CEO |
| Manager B | CEO |
| Employee X | Manager A|

JOIN Visual Summary (Venn Diagram)

INNER JOIN       =  (A ∩ B)
LEFT JOIN        =  (A) + (A ∩ B)
RIGHT JOIN       =  (B) + (A ∩ B)
FULL JOIN        =  (A) + (B) + (A ∩ B)
CROSS JOIN       =  (A × B)

JOIN vs WHERE (Old Style)

Old Style (MySQL 3.x):

SELECT s.name, d.dept_name
FROM students s, departments d
WHERE s.dept_id = d.dept_id;

Same as INNER JOIN
Avoid – less readable, harder to mix with OUTER JOIN

Best Practices & Tips

Performance: EXPLAIN Your JOIN

EXPLAIN 
SELECT s.name, d.dept_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

Look for:
- type: ref or eq_ref → good
- rows → estimate
- key → index used?

Common Interview Questions

Practice Queries (Try These!)

-- 1. Students with no department
SELECT name FROM students 
LEFT JOIN departments ON students.dept_id = departments.dept_id
WHERE departments.dept_id IS NULL;

-- 2. Departments with no students
SELECT dept_name FROM departments
LEFT JOIN students ON departments.dept_id = students.dept_id
WHERE students.id IS NULL;

-- 3. All combinations of students and departments
SELECT name, dept_name FROM students CROSS JOIN departments;

Summary Table

You now master all MySQL JOIN types!
Practice with real data – it’s the key to fluency.

Want a downloadable PDF cheat sheet or quiz? Just ask!