Harsh Mishra

Posted on Feb 19

Complete Guide to MySQL Joins

#mysql #sql #database #beginners

The Complete Guide to MySQL Joins: A Comprehensive Explanation

MySQL joins are fundamental for working with relational databases where data is stored across multiple tables. Joins allow you to combine rows from two or more tables based on related columns. In this guide, we will cover all types of MySQL joins, with real-world examples, SQL queries, and sample output.

1. Understanding MySQL Joins

A join in MySQL is used to retrieve data from multiple tables based on a common column. Joins help eliminate redundancy and keep databases normalized.

Types of MySQL Joins

INNER JOIN – Returns only matching records from both tables.
LEFT JOIN (LEFT OUTER JOIN) – Returns all records from the left table and matching records from the right.
RIGHT JOIN (RIGHT OUTER JOIN) – Returns all records from the right table and matching records from the left.
FULL OUTER JOIN – Returns all records when there is a match in either table (not directly supported in MySQL, but can be achieved with UNION).
CROSS JOIN – Returns the Cartesian product of both tables.
SELF JOIN – Joins a table to itself.
NATURAL JOIN – Automatically joins tables based on common column names.

2. Creating Sample Database & Tables

Before we perform joins, let's create a database and define sample tables.

Step 1: Create a Database

CREATE DATABASE CompanyDB;
USE CompanyDB;

Step 2: Create Employees and Departments Tables

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

Departments contains department details.
Employees stores employee information and references Departments.

3. INNER JOIN (Default Join)

✅ Definition

INNER JOIN returns only the matching rows between both tables.

SQL Query

SELECT Employees.employee_id, Employees.name, Employees.salary, Departments.department_name
FROM Employees
INNER JOIN Departments ON Employees.department_id = Departments.department_id;

Example Output

employee_id	name	salary	department_name
101	Alice	5000.00	HR
102	Bob	6000.00	IT
103	Charlie	7000.00	Finance

🔹 Explanation: Only employees with a valid department_id in the Departments table are included.

4. LEFT JOIN (LEFT OUTER JOIN)

✅ Definition

LEFT JOIN returns all rows from the left table (Employees) and matching rows from the right (Departments). If no match is found, NULL is returned.

SQL Query

SELECT Employees.employee_id, Employees.name, Employees.salary, Departments.department_name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.department_id;

Example Output

employee_id	name	salary	department_name
101	Alice	5000.00	HR
102	Bob	6000.00	IT
103	Charlie	7000.00	Finance
104	David	4500.00	NULL

🔹 Explanation:

David does not have a department, so NULL appears for department_name.

5. RIGHT JOIN (RIGHT OUTER JOIN)

✅ Definition

RIGHT JOIN returns all rows from the right table (Departments) and matching rows from the left (Employees). If no match is found, NULL is returned.

SQL Query

SELECT Employees.employee_id, Employees.name, Employees.salary, Departments.department_name
FROM Employees
RIGHT JOIN Departments ON Employees.department_id = Departments.department_id;

Example Output

employee_id	name	salary	department_name
101	Alice	5000.00	HR
102	Bob	6000.00	IT
103	Charlie	7000.00	Finance
NULL	NULL	NULL	Marketing

🔹 Explanation:

The Marketing department has no employees, so NULL appears for employee_id, name, and salary.

6. FULL OUTER JOIN (Using UNION)

✅ Definition

MySQL does not support FULL OUTER JOIN directly, but we can achieve it using UNION.

SQL Query

SELECT Employees.employee_id, Employees.name, Employees.salary, Departments.department_name
FROM Employees
LEFT JOIN Departments ON Employees.department_id = Departments.department_id
UNION
SELECT Employees.employee_id, Employees.name, Employees.salary, Departments.department_name
FROM Employees
RIGHT JOIN Departments ON Employees.department_id = Departments.department_id;

Example Output

employee_id	name	salary	department_name
101	Alice	5000.00	HR
102	Bob	6000.00	IT
103	Charlie	7000.00	Finance
104	David	4500.00	NULL
NULL	NULL	NULL	Marketing

7. CROSS JOIN (Cartesian Product)

✅ Definition

CROSS JOIN returns the Cartesian product of both tables, meaning every row in one table is matched with every row in the other.

SQL Query

SELECT Employees.name, Departments.department_name
FROM Employees
CROSS JOIN Departments;

Example Output

name	department_name
Alice	HR
Alice	IT
Alice	Finance
Alice	Marketing
Bob	HR
Bob	IT
Bob	Finance
Bob	Marketing

🔹 Explanation: If Employees has 4 rows and Departments has 4 rows, the result will have 4 × 4 = 16 rows.

8. SELF JOIN

✅ Definition

A SELF JOIN is a join where a table is joined with itself.

Example Use Case: Employee Manager Relationship

SELECT A.employee_id AS Employee_ID, A.name AS Employee_Name, B.name AS Manager_Name
FROM Employees A
LEFT JOIN Employees B ON A.manager_id = B.employee_id;

🔹 Explanation: We join Employees to itself to find each employee’s manager.

9. NATURAL JOIN

✅ Definition

NATURAL JOIN automatically joins tables based on common column names.

SQL Query

SELECT * FROM Employees NATURAL JOIN Departments;

🔹 Explanation: If both tables have a column with the same name, they are joined automatically.

Summary of MySQL Join Syntax

Joins in MySQL allow you to retrieve data from multiple tables based on related columns. Below is a summary of different types of joins along with their syntax and important considerations.

1. INNER JOIN (Default Join)

Retrieves only matching rows from both tables based on the specified condition.
ON is required.
Syntax:

  SELECT column_names
  FROM table1
  INNER JOIN table2 ON table1.common_column = table2.common_column;

2. LEFT JOIN (LEFT OUTER JOIN)

Retrieves all records from the left table and matching records from the right table. If there is no match, NULL values are returned for the right table’s columns.
ON is required.
Syntax:

  SELECT column_names
  FROM table1
  LEFT JOIN table2 ON table1.common_column = table2.common_column;

3. RIGHT JOIN (RIGHT OUTER JOIN)

Retrieves all records from the right table and matching records from the left table. If there is no match, NULL values are returned for the left table’s columns.
ON is required.
Syntax:

  SELECT column_names
  FROM table1
  RIGHT JOIN table2 ON table1.common_column = table2.common_column;

4. FULL OUTER JOIN (Simulated Using UNION in MySQL)

Retrieves all records from both tables. If a record does not have a match in the other table, NULL values are returned for the missing columns.
MySQL does not support FULL OUTER JOIN directly, so it is simulated using UNION.
ON is required.
Syntax:

  SELECT column_names
  FROM table1
  LEFT JOIN table2 ON table1.common_column = table2.common_column
  UNION
  SELECT column_names
  FROM table1
  RIGHT JOIN table2 ON table1.common_column = table2.common_column;

5. CROSS JOIN (Cartesian Product)

Returns all possible combinations of rows from both tables (Cartesian product).
ON is not used and cannot be used.
Syntax:

  SELECT column_names
  FROM table1
  CROSS JOIN table2;

Alternative Using JOIN Without ON:

  SELECT column_names
  FROM table1, table2;

(Equivalent to CROSS JOIN)

6. SELF JOIN (Joining a Table with Itself)

Joins a table to itself, treating it as if it were two different tables.
ON is required.
Syntax:

  SELECT A.column_name, B.column_name
  FROM table_name A
  JOIN table_name B ON A.common_column = B.common_column;

7. NATURAL JOIN (Automatic Join Based on Common Column Names)

Automatically joins tables based on columns with the same name.
ON is not used and cannot be used.
Syntax:

  SELECT column_names
  FROM table1
  NATURAL JOIN table2;

Key Considerations

ON is required for INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and SELF JOIN.
ON is not used and cannot be used with NATURAL JOIN and CROSS JOIN.
FULL OUTER JOIN is not directly supported in MySQL and must be simulated using UNION.
CROSS JOIN does not require an ON condition because it produces a Cartesian product.

Conclusion

INNER JOIN → Returns only matching rows.
LEFT JOIN → Returns all left table rows + matched right table rows.
RIGHT JOIN → Returns all right table rows + matched left table rows.
FULL JOIN → Returns all records from both tables (with UNION).
CROSS JOIN → Returns Cartesian product (all possible combinations).
SELF JOIN → Joins a table with itself.
NATURAL JOIN → Auto-matches based on common column names.

The Complete Guide to MySQL Joins: A Comprehensive Explanation

1. Understanding MySQL Joins

Types of MySQL Joins

2. Creating Sample Database & Tables

Step 1: Create a Database

Step 2: Create Employees and Departments Tables

3. INNER JOIN (Default Join)

✅ Definition

SQL Query

Example Output

4. LEFT JOIN (LEFT OUTER JOIN)

✅ Definition

SQL Query

Example Output

5. RIGHT JOIN (RIGHT OUTER JOIN)

✅ Definition

SQL Query

Example Output

6. FULL OUTER JOIN (Using UNION)

✅ Definition

SQL Query

Example Output

7. CROSS JOIN (Cartesian Product)

✅ Definition

SQL Query

Example Output

8. SELF JOIN

✅ Definition

Example Use Case: Employee Manager Relationship

9. NATURAL JOIN

✅ Definition

SQL Query

Summary of MySQL Join Syntax

1. INNER JOIN (Default Join)

2. LEFT JOIN (LEFT OUTER JOIN)

3. RIGHT JOIN (RIGHT OUTER JOIN)

4. FULL OUTER JOIN (Simulated Using UNION in MySQL)

5. CROSS JOIN (Cartesian Product)

6. SELF JOIN (Joining a Table with Itself)

7. NATURAL JOIN (Automatic Join Based on Common Column Names)

Key Considerations

Conclusion

Read next

EC2 Configuration options

Amazon Aurora Global Database – A Deep Dive

Have You Ever Noticed Preflight Requests?

Asas Kubernetes - Hari 4. Bagaimana pentadbir Kubernetes mengawalselia sumber komponen.