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:

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

name	dept_name
Alice	Computer Science
Alice	Mathematics
Alice	Physics
Bob	Computer Science
...	...

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

Tip	Explanation
Use explicit JOIN syntax	FROM A JOIN B ON ... > comma style
Always use table aliases	s JOIN d ON s.id = d.id
Put ON condition right after JOIN	Improves readability
Use LEFT JOIN for "include all from left"	Most common in reports
Index foreign keys	Speeds up JOINs

---

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

Question	Answer
Difference between INNER and LEFT JOIN?	INNER: only matches. LEFT: all from left + matches
How to get non-matching rows?	LEFT JOIN ... WHERE right_column IS NULL
Can you JOIN more than 2 tables?	Yes! Chain them: A JOIN B JOIN C
What is a Cartesian Join?	CROSS JOIN or missing ON clause

---

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

JOIN Type	Matches	Left Table	Right Table	MySQL Support
INNER JOIN	Only matches	Partial	Partial	Yes
LEFT JOIN	All left + matches	Full	Partial	Yes
RIGHT JOIN	All right + matches	Partial	Full	Yes
FULL JOIN	All from both	Full	Full	No (use UNION)
CROSS JOIN	All combinations	Full	Full	Yes
SELF JOIN	Table to itself	Yes	Yes	Yes

---

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!