SQL JOIN Tutorial: INNER, LEFT, RIGHT & FULL JOIN

SQL JOIN Tutorial: INNER, LEFT, RIGHT & FULL JOIN Explained

📖 Introduction: Why JOINs Exist

Real-world data is relational and spread across multiple tables. A customer places orders. Orders contain products. Products belong to categories. Employees work in departments. If you stored everything in one giant table, you'd have massive data duplication and endless inconsistencies.

JOINs are the bridge. They let you query data from two or more tables in a single result set based on a related column.

Analogy: Imagine two filing cabinets. One has employee names with department IDs. The other has department IDs with department names. A JOIN is the act of looking at both cabinets simultaneously to produce a complete employee directory.

🗄️ Understanding Table Relationships

Before writing JOINs, you must understand how tables relate:

Relationship	Meaning	Example
One-to-One (1:1)	One row in Table A matches exactly one row in Table B	Employee ↔ Passport (one employee has one passport)
One-to-Many (1:N)	One row in Table A matches many rows in Table B	Department → Employees (one department has many employees)
Many-to-Many (M:N)	Many rows in Table A match many rows in Table B	Students ↔ Courses (students take many courses, courses have many students)

M:N relationships require a junction table (also called a linking or bridge table) to break them into two 1:N relationships.

🛠️ Setting Up Practice Data

Let's create a realistic company database with multiple related tables.

sql
1-- Create database
2CREATE DATABASE company_db;
3USE company_db;
4
5-- Departments table (Parent)
6CREATE TABLE departments (
7    dept_id INT PRIMARY KEY AUTO_INCREMENT,
8    dept_name VARCHAR(50) NOT NULL,
9    location VARCHAR(50)
10);
11
12-- Employees table (Child of Departments)
13CREATE TABLE employees (
14    emp_id INT PRIMARY KEY AUTO_INCREMENT,
15    emp_name VARCHAR(100) NOT NULL,
16    salary DECIMAL(10,2),
17    dept_id INT,
18    manager_id INT,
19    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
20);
21
22-- Customers table
23CREATE TABLE customers (
24    customer_id INT PRIMARY KEY AUTO_INCREMENT,
25    customer_name VARCHAR(100),
26    city VARCHAR(50)
27);
28
29-- Products table
30CREATE TABLE products (
31    product_id INT PRIMARY KEY AUTO_INCREMENT,
32    product_name VARCHAR(100),
33    price DECIMAL(10,2)
34);
35
36-- Orders table (links Customers and Products via junction)
37CREATE TABLE orders (
38    order_id INT PRIMARY KEY AUTO_INCREMENT,
39    customer_id INT,
40    order_date DATE,
41    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
42);
43
44-- Order Items (junction table for M:N between Orders and Products)
45CREATE TABLE order_items (
46    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
47    order_id INT,
48    product_id INT,
49    quantity INT,
50    FOREIGN KEY (order_id) REFERENCES orders(order_id),
51    FOREIGN KEY (product_id) REFERENCES products(product_id)
52);

Insert Sample Data

sql
1-- Departments
2INSERT INTO departments (dept_name, location) VALUES
3('IT', 'Building A'),
4('HR', 'Building B'),
5('Sales', 'Building C'),
6('Marketing', 'Building D');
7
8-- Employees (note: emp_id 4 has no department yet)
9INSERT INTO employees (emp_name, salary, dept_id, manager_id) VALUES
10('Alice Johnson', 75000, 1, NULL),
11('Bob Smith', 55000, 2, 1),
12('Carol White', 80000, 1, 1),
13('David Brown', 90000, 3, 1),
14('Eve Davis', 55000, 2, 1),
15('Frank Miller', 75000, 1, 3),
16('Grace Lee', NULL, NULL, 1);   -- No department, no salary yet
17
18-- Customers
19INSERT INTO customers (customer_name, city) VALUES
20('Acme Corp', 'New York'),
21('Globex', 'Los Angeles'),
22('Initech', 'Chicago');
23
24-- Products
25INSERT INTO products (product_name, price) VALUES
26('Laptop', 1200.00),
27('Mouse', 25.00),
28('Keyboard', 150.00);
29
30-- Orders
31INSERT INTO orders (customer_id, order_date) VALUES
32(1, '2026-08-01'),
33(1, '2026-08-10'),
34(2, '2026-08-05');
35
36-- Order Items
37INSERT INTO order_items (order_id, product_id, quantity) VALUES
38(1, 1, 2),
39(1, 2, 5),
40(2, 3, 1),
41(3, 1, 1),
42(3, 3, 2);

1️⃣ INNER JOIN — Matching Rows Only

Purpose: Returns only rows where there is a match in both tables. If a row exists in one table but not the other, it is excluded.

Visual: Think of the intersection of two circles in a Venn diagram.

Syntax:

sql
1SELECT columns
2FROM table1
3INNER JOIN table2 ON table1.column = table2.column;

Example 1: Employees with Their Departments

sql
1SELECT 
2    e.emp_name,
3    d.dept_name,
4    d.location
5FROM employees e
6INNER JOIN departments d ON e.dept_id = d.dept_id;

What happens: SQL looks at each employee, finds their dept_id, and matches it to departments.dept_id.

Output:

+---------------+-----------+------------+
| emp_name      | dept_name | location   |
+---------------+-----------+------------+
| Alice Johnson | IT        | Building A |
| Bob Smith     | HR        | Building B |
| Carol White   | IT        | Building A |
| David Brown   | Sales     | Building C |
| Eve Davis     | HR        | Building B |
| Frank Miller  | IT        | Building A |
+---------------+-----------+------------+

Notice: Grace Lee is missing! She has dept_id = NULL, so there is no match in the departments table. INNER JOIN excludes non-matching rows.

Example 2: INNER JOIN with WHERE

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5INNER JOIN departments d ON e.dept_id = d.dept_id
6WHERE d.location = 'Building A';

Output: Only IT department employees.

2️⃣ LEFT JOIN (LEFT OUTER JOIN) — All from Left, Matching from Right

Purpose: Returns all rows from the left table, and matching rows from the right table. If no match, right table columns show NULL.

Visual: The entire left circle of the Venn diagram.

Syntax:

sql
1SELECT columns
2FROM table1
3LEFT JOIN table2 ON table1.column = table2.column;

Example 3: All Employees (Even Without Departments)

sql
1SELECT 
2    e.emp_name,
3    COALESCE(d.dept_name, 'No Department') AS department,
4    d.location
5FROM employees e
6LEFT JOIN departments d ON e.dept_id = d.dept_id;

Output:

+---------------+----------------+------------+
| emp_name      | department     | location   |
+---------------+----------------+------------+
| Alice Johnson | IT             | Building A |
| Bob Smith     | HR             | Building B |
| Carol White   | IT             | Building A |
| David Brown   | Sales          | Building C |
| Eve Davis     | HR             | Building B |
| Frank Miller  | IT             | Building A |
| Grace Lee     | No Department  | NULL       |
+---------------+----------------+------------+

Key Insight: Use LEFT JOIN when you want to see all records from the primary table, even if related data is missing. COALESCE(d.dept_name, 'No Department') replaces NULL with readable text.

3️⃣ RIGHT JOIN (RIGHT OUTER JOIN) — All from Right, Matching from Left

Purpose: Returns all rows from the right table, and matching rows from the left. If no match, left table columns show NULL.

Visual: The entire right circle of the Venn diagram.

Note: RIGHT JOIN is simply the mirror of LEFT JOIN. Most developers prefer LEFT JOIN and swap table order instead.

Example 4: All Departments (Even Without Employees)

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Output:

+---------------+-----------+
| emp_name      | dept_name |
+---------------+-----------+
| Alice Johnson | IT        |
| Bob Smith     | HR        |
| Carol White   | IT        |
| David Brown   | Sales     |
| Eve Davis     | HR        |
| Frank Miller  | IT        |
| NULL          | Marketing |
+---------------+-----------+

Notice: Marketing has no employees, but it still appears because it's on the right side. emp_name is NULL for that row.

4️⃣ FULL JOIN (FULL OUTER JOIN) — All Rows from Both Tables

Purpose: Returns all rows from both tables. Matches are combined; non-matches show NULL for the missing side.

Visual: The entire Venn diagram — both circles fully shaded.

MySQL does not support FULL JOIN directly. You must simulate it with UNION.

Example 5: FULL JOIN Using UNION (MySQL Compatible)

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5LEFT JOIN departments d ON e.dept_id = d.dept_id
6
7UNION
8
9SELECT 
10    e.emp_name,
11    d.dept_name
12FROM employees e
13RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Output:

+---------------+-----------+
| emp_name      | dept_name |
+---------------+-----------+
| Alice Johnson | IT        |
| Bob Smith     | HR        |
| Carol White   | IT        |
| David Brown   | Sales     |
| Eve Davis     | HR        |
| Frank Miller  | IT        |
| Grace Lee     | NULL      |
| NULL          | Marketing |
+---------------+-----------+

Explanation: The first query gets all employees (LEFT JOIN). The second gets all departments (RIGHT JOIN). UNION combines them, removing duplicates. This gives us the full picture: Grace (no dept) and Marketing (no employees) are both visible.

5️⃣ CROSS JOIN — Cartesian Product

Purpose: Returns every possible combination of rows from both tables. If Table A has 4 rows and Table B has 3 rows, you get 12 rows.

Use Case: Generating combinations (e.g., all products × all regions for a sales matrix).

Syntax:

sql
1SELECT * FROM table1 CROSS JOIN table2;
2-- OR
3SELECT * FROM table1, table2;

Example 6: All Employees × All Departments

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5CROSS JOIN departments d;

Partial Output:

+---------------+-----------+
| emp_name      | dept_name |
+---------------+-----------+
| Alice Johnson | IT        |
| Alice Johnson | HR        |
| Alice Johnson | Sales     |
| Alice Johnson | Marketing |
| Bob Smith     | IT        |
| Bob Smith     | HR        |
| ...           | ...       |
+---------------+-----------+

Warning: CROSS JOINs can produce massive result sets. Use with caution!

6️⃣ SELF JOIN — Joining a Table to Itself

Purpose: Query hierarchical or comparative data within the same table.

Use Case: Employee-Manager relationships, finding duplicates, comparing rows.

Syntax: You must use table aliases to treat the same table as two different tables.

Example 7: Find Each Employee's Manager

sql
1SELECT 
2    e1.emp_name AS employee,
3    e2.emp_name AS manager
4FROM employees e1
5LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

What happens:

e1 represents the employee
e2 represents the manager
We join e1.manager_id to e2.emp_id

Output:

+---------------+---------------+
| employee      | manager       |
+---------------+---------------+
| Alice Johnson | NULL          |
| Bob Smith     | Alice Johnson |
| Carol White   | Alice Johnson |
| David Brown   | Alice Johnson |
| Eve Davis     | Alice Johnson |
| Frank Miller  | Carol White   |
| Grace Lee     | Alice Johnson |
+---------------+---------------+

Alice has no manager (NULL), so she is the CEO. Frank reports to Carol, not Alice.

7️⃣ Joining Multiple Tables (3+ Tables)

Purpose: Real queries often need data from 3, 4, or 5+ tables chained together.

Rule: Add one JOIN clause for each additional table.

Example 8: Orders with Customer and Product Details (3 Tables)

sql
1SELECT 
2    o.order_id,
3    c.customer_name,
4    p.product_name,
5    oi.quantity,
6    (oi.quantity * p.price) AS line_total
7FROM orders o
8INNER JOIN customers c ON o.customer_id = c.customer_id
9INNER JOIN order_items oi ON o.order_id = oi.order_id
10INNER JOIN products p ON oi.product_id = p.product_id;

Output:

+----------+---------------+--------------+----------+------------+
| order_id | customer_name | product_name | quantity | line_total |
+----------+---------------+--------------+----------+------------+
| 1        | Acme Corp     | Laptop       | 2        | 2400.00    |
| 1        | Acme Corp     | Mouse        | 5        | 125.00     |
| 2        | Acme Corp     | Keyboard     | 1        | 150.00     |
| 3        | Globex        | Laptop       | 1        | 1200.00    |
| 3        | Globex        | Keyboard     | 2        | 300.00     |
+----------+---------------+--------------+----------+------------+

Reading the chain: Start from orders → get customers → get order_items → get products. Each JOIN adds more context.

Example 9: Complete Report with 5 Tables

sql
1SELECT 
2    o.order_id,
3    c.customer_name,
4    c.city,
5    p.product_name,
6    p.price,
7    oi.quantity,
8    (oi.quantity * p.price) AS line_total,
9    d.dept_name AS processed_by_dept
10FROM orders o
11INNER JOIN customers c ON o.customer_id = c.customer_id
12INNER JOIN order_items oi ON o.order_id = oi.order_id
13INNER JOIN products p ON oi.product_id = p.product_id
14LEFT JOIN employees e ON o.order_id = e.emp_id   -- hypothetical link
15LEFT JOIN departments d ON e.dept_id = d.dept_id;

8️⃣ USING vs. ON Clause

When two tables share a column with the exact same name, you can simplify the syntax with USING.

Approach	Syntax	When to Use
`ON`	`ON a.id = b.id`	Column names differ, or complex conditions
`USING`	`USING (id)`	Column names are identical in both tables

Example 10: ON Clause (Explicit)

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5INNER JOIN departments d ON e.dept_id = d.dept_id;

Example 11: USING Clause (Shorthand)

If both tables had a column literally named dept_id (which they do):

sql
1SELECT 
2    e.emp_name,
3    d.dept_name
4FROM employees e
5INNER JOIN departments d USING (dept_id);

Benefits of USING:

Cleaner syntax

The joined column appears only once in the result (not duplicated as e.dept_id and d.dept_id)

Works only when column names match exactly

📝 Complete JOIN Reference

JOIN Type	Returns	Use When
`INNER JOIN`	Only matching rows	You only want data that exists in both tables
`LEFT JOIN`	All left + matching right	You want all primary records, even without matches
`RIGHT JOIN`	Matching left + all right	You want all reference records, even unused
`FULL JOIN`	All rows from both	You want everything from both sides (use UNION in MySQL)
`CROSS JOIN`	Every combination	You need to generate all possible pairs
`SELF JOIN`	Table joined to itself	You have hierarchical or comparative data

🚀 Hands-On Project: Reporting Dashboard (5+ Tables)

Project Goal

Build a comprehensive sales dashboard query pulling data from 5 related tables.

The Schema

departments → employees → orders → customers
orders → order_items → products

Dashboard Query

sql
1SELECT 
2    d.dept_name AS sales_department,
3    e.emp_name AS sales_rep,
4    c.customer_name,
5    c.city AS customer_city,
6    o.order_id,
7    o.order_date,
8    p.product_name,
9    p.price AS unit_price,
10    oi.quantity,
11    (oi.quantity * p.price) AS line_total
12FROM orders o
13INNER JOIN customers c ON o.customer_id = c.customer_id
14INNER JOIN order_items oi ON o.order_id = oi.order_id
15INNER JOIN products p ON oi.product_id = p.product_id
16LEFT JOIN employees e ON o.order_id % 4 + 1 = e.emp_id   -- Simulated assignment
17LEFT JOIN departments d ON e.dept_id = d.dept_id
18ORDER BY o.order_date DESC, line_total DESC;

Summary Aggregates for the Dashboard

sql
1-- Total revenue by department
2SELECT 
3    COALESCE(d.dept_name, 'Unassigned') AS department,
4    COUNT(DISTINCT o.order_id) AS total_orders,
5    SUM(oi.quantity * p.price) AS total_revenue
6FROM orders o
7INNER JOIN order_items oi ON o.order_id = oi.order_id
8INNER JOIN products p ON oi.product_id = p.product_id
9LEFT JOIN employees e ON o.order_id % 4 + 1 = e.emp_id
10LEFT JOIN departments d ON e.dept_id = d.dept_id
11GROUP BY d.dept_name
12ORDER BY total_revenue DESC;
13
14-- Top customers by spend
15SELECT 
16    c.customer_name,
17    COUNT(o.order_id) AS order_count,
18    SUM(oi.quantity * p.price) AS total_spent
19FROM customers c
20LEFT JOIN orders o ON c.customer_id = o.customer_id
21LEFT JOIN order_items oi ON o.order_id = oi.order_id
22LEFT JOIN products p ON oi.product_id = p.product_id
23GROUP BY c.customer_id, c.customer_name
24ORDER BY total_spent DESC;

✅ Module 7 Summary

Concept	Command	Remember
Match both tables	`INNER JOIN`	Excludes non-matching rows
All left records	`LEFT JOIN`	Primary table stays intact
All right records	`RIGHT JOIN`	Reference table stays intact
All records both sides	`FULL JOIN` (or `UNION`)	MySQL needs `LEFT UNION RIGHT`
All combinations	`CROSS JOIN`	Can explode result size
Same-table join	`SELF JOIN`	Use aliases `e1` and `e2`
Multiple tables	Chain `JOIN`s	Add one JOIN per table
Same column names	`USING (col)`	Cleaner than `ON a.col = b.col`

🎯 Practice Exercises

Write an INNER JOIN to show only employees who have a department.
Write a LEFT JOIN to show all departments and employee counts (including empty departments).
Use a SELF JOIN to find employees who earn more than their manager.
Join orders, customers, order_items, and products to show every line item with customer and product names.
Write a query using USING instead of ON for the employee-department join.
Create a FULL JOIN simulation using UNION for employees and departments.
Explain why CROSS JOIN between a 100-row table and a 100-row table is dangerous.

🎓 What's Next?

In Module 8, you'll master Subqueries and CTEs (Common Table Expressions) — writing queries inside queries. You'll learn to solve complex problems like "Find employees who earn above the company average" and build recursive queries for hierarchical data! 🚀