How To Use MySQL JOINS with Examples

MySQL, one of the most popular relational database management systems, is essential for data manipulation and retrieval in various applications. In relational databases, data is typically spread across multiple tables, and JOINS are fundamental tools that allow users to retrieve related data from these tables in a single query.

MySQL JOINS is a powerful tool for combining rows from two or more tables based on a related column between them. In this article, we will explore the different types of MySQL JOINS and provide practical examples to illustrate their usage.

MySQL JOINS Types

MySQL database supports several types of JOINS to facilitate different ways of combining data from multiple tables:

1. INNER JOIN: Retrieves records that have matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Retrieves all records from the left table and the matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Retrieves all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table.
4. FULL JOIN (or FULL OUTER JOIN): Retrieves records when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table without the match.
5. CROSS JOIN: Returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from the two tables.
6. SELF JOIN: Joins a table with itself.

Practical Examples

Let’s create two sample tables, employees, and departments to demonstrate how JOINS work. To MySQL JOINS explained, we will use the MySQL Workbench interface.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

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

INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES
(1, 'John', 'Doe', 1),
(2, 'Jane', 'Smith', 2),
(3, 'Sam', 'Brown', 1),
(4, 'Sara', 'Davis', 3);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'Engineering');

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. Here’s how to use an INNER JOIN to retrieve a list of employees along with their department names:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN

The LEFT JOIN keyword returns all records from the left table (employees), and the matched records from the right table (departments). If there is no match, the result is NULL on the right side.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN

The RIGHT JOIN keyword returns all records from the right table (departments) and the matched records from the left table (employees). If there is no match, the result is NULL on the left side.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

FULL JOIN

MySQL does not support the FULL JOIN keyword directly. However, you can achieve the same result using a combination of LEFT JOIN and RIGHT JOIN with UNION.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

CROSS JOIN

The CROSS JOIN keyword returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows from the two tables.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
CROSS JOIN departments;

SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. This can be useful for hierarchical data or comparing rows within the same table.

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    manager_id INT
);
INSERT INTO employee (employee_id, first_name, manager_id) VALUES
(1, 'John', NULL),
(2, 'Jane', 1),
(3, 'Sam', 1),
(4, 'Sara', 2);
SELECT e1.first_name AS Employee, e2.first_name AS Manager
FROM employee e1
LEFT JOIN employee e2 ON e1.manager_id = e2.employee_id;

Above we created another example with an employee table that has a manager_id column to demonstrate a SELF JOIN.

Conclusion

MySQL JOINS are powerful tools for retrieving related data from multiple tables. Whether you’re combining rows with INNER JOIN, including all rows from one table with LEFT JOIN or RIGHT JOIN, or performing more complex operations with FULL JOIN, CROSS JOIN, or SELF JOIN, mastering these operations is essential for efficient database management. Practice these examples to gain a deeper understanding and enhance your database querying skills.

FAQ