Introduction
In this chapter, we will learn about various types of joins in MySQL. Joins are used to combine rows from two or more tables based on a related column between them. Joins are essential for retrieving data that is spread across multiple tables. We will cover different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN, along with their syntax, examples, and important considerations.
Types of Joins
INNER JOIN
An INNER JOIN returns only the rows that have matching values in both tables.
Syntax
SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;
Example
SELECT students.first_name, students.last_name, departments.department_name
FROM students
INNER JOIN departments ON students.department_id = departments.id;
LEFT JOIN (or LEFT OUTER JOIN)
A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;
Example
SELECT students.first_name, students.last_name, departments.department_name
FROM students
LEFT JOIN departments ON students.department_id = departments.id;
RIGHT JOIN (or RIGHT OUTER JOIN)
A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
Example
SELECT students.first_name, students.last_name, departments.department_name
FROM students
RIGHT JOIN departments ON students.department_id = departments.id;
FULL OUTER JOIN
A FULL OUTER JOIN returns all rows when there is a match in either left or right table. If there is no match, the result is NULL from the side where there is no match.
MySQL does not support the FULL OUTER JOIN directly, but it can be simulated using UNION.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;
Example
SELECT students.first_name, students.last_name, departments.department_name
FROM students
LEFT JOIN departments ON students.department_id = departments.id
UNION
SELECT students.first_name, students.last_name, departments.department_name
FROM students
RIGHT JOIN departments ON students.department_id = departments.id;
CROSS JOIN
A CROSS JOIN returns the Cartesian product of the two tables, i.e., it returns all possible combinations of rows.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;
Example
SELECT students.first_name, students.last_name, departments.department_name
FROM students
CROSS JOIN departments;
SELF JOIN
A SELF JOIN is a join of a table to itself. This can be useful for querying hierarchical data or comparing rows within the same table.
Syntax
SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b ON a.common_column = b.common_column;
Example
SELECT a.first_name AS student1_first_name, b.first_name AS student2_first_name
FROM students a
JOIN students b ON a.department_id = b.department_id;
Full Example
Let’s go through a full example where we create tables, insert data into them, and use different types of joins to retrieve records.
- Create a Database:
CREATE DATABASE school;
- Select the Database:
USE school;
- Create Tables:
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_date DATE,
department_id INT
);
CREATE TABLE departments (
id INT PRIMARY KEY AUTO_INCREMENT,
department_name VARCHAR(100) NOT NULL
);
- Insert Data into the Tables:
INSERT INTO students (first_name, last_name, email, enrollment_date, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01', 1),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03', 1),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', '2023-07-05', NULL);
INSERT INTO departments (department_name) VALUES
('Mathematics'),
('Science'),
('Literature');
- INNER JOIN Example:
SELECT students.first_name, students.last_name, departments.department_name
FROM students
INNER JOIN departments ON students.department_id = departments.id;
Output
| first_name | last_name | department_name |
|---|---|---|
| Rahul | Sharma | Mathematics |
| Priya | Singh | Science |
| Amit | Kumar | Mathematics |
| Neha | Verma | Literature |
- LEFT JOIN Example:
SELECT students.first_name, students.last_name, departments.department_name
FROM students
LEFT JOIN departments ON students.department_id = departments.id;
Output
| first_name | last_name | department_name |
|---|---|---|
| Rahul | Sharma | Mathematics |
| Priya | Singh | Science |
| Amit | Kumar | Mathematics |
| Neha | Verma | Literature |
| Sahil | Mehta | NULL |
- RIGHT JOIN Example:
SELECT students.first_name, students.last_name, departments.department_name
FROM students
RIGHT JOIN departments ON students.department_id = departments.id;
Output
| first_name | last_name | department_name |
|---|---|---|
| Rahul | Sharma | Mathematics |
| Priya | Singh | Science |
| Amit | Kumar | Mathematics |
| Neha | Verma | Literature |
| NULL | NULL | Mathematics |
| NULL | NULL | Science |
| NULL | NULL | Literature |
- FULL OUTER JOIN Example:
SELECT students.first_name, students.last_name, departments.department_name
FROM students
LEFT JOIN departments ON students.department_id = departments.id
UNION
SELECT students.first_name, students.last_name, departments.department_name
FROM students
RIGHT JOIN departments ON students.department_id = departments.id;
Output
| first_name | last_name | department_name |
|---|---|---|
| Rahul | Sharma | Mathematics |
| Priya | Singh | Science |
| Amit | Kumar | Mathematics |
| Neha | Verma | Literature |
| Sahil | Mehta | NULL |
| NULL | NULL | Mathematics |
| NULL | NULL | Science |
| NULL | NULL | Literature |
- CROSS JOIN Example:
SELECT students.first_name, students.last_name, departments.department_name
FROM students
CROSS JOIN departments;
Output
| first_name | last_name | department_name |
|---|---|---|
| Rahul | Sharma | Mathematics |
| Rahul | Sharma | Science |
| Rahul | Sharma | Literature |
| Priya | Singh | Mathematics |
| Priya | Singh | Science |
| Priya | Singh | Literature |
| Amit | Kumar | Mathematics |
| Amit | Kumar | Science |
| Amit | Kumar | Literature |
| Neha | Verma | Mathematics |
| Neha | Verma | Science |
| Neha | Verma | Literature |
| Sahil | Mehta | Mathematics |
| Sahil | Mehta | Science |
| Sahil | Mehta | Literature |
- SELF JOIN Example:
SELECT a.first_name AS student1_first_name, b.first_name AS student2_first_name
FROM students a
JOIN students b ON a.department_id = b.department_id
WHERE a.id != b.id;
Output
| student1_first_name | student2_first_name |
|---|---|
| Rahul | Amit |
| Amit | Rahul |
Important Considerations
- Referential Integrity: Ensure that foreign keys are properly defined to maintain referential integrity between related tables.
- Performance: Joins can impact performance, especially with large datasets. Ensure
appropriate indexing and optimize queries to improve performance.
- NULL Values: Be aware of
NULLvalues in the result set, especially when usingLEFT JOINandRIGHT JOIN.
Conclusion
Joins are essential for retrieving related data from multiple tables in MySQL. This chapter covered different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN. We provided examples with SELECT statements and discussed important considerations.