MySQL Joins

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.

  1. Create a Database:
CREATE DATABASE school;
  1. Select the Database:
USE school;
  1. 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
);
  1. 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');
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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 NULL values in the result set, especially when using LEFT JOIN and RIGHT 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top