MySQL JOIN

MySQL JOIN is a fundamental feature that combines rows from two or more tables based on a related column between them. It allows for efficient data retrieval by enabling the extraction of related information from multiple tables in a single query.

In this article, We will learn about MySQL JOIN by understanding various types of JOINS with the help of examples and so on.

MySQL JOIN

• A MySQL JOIN is a method to combine data from two or more tables in a database based on a related column between them.
  
• JOINs allow us to retrieve related data from multiple tables in a single query, avoiding the need for numerous separate queries.
  
• JOINs help maintain referential integrity by ensuring that relationships between tables are respected and data consistency is preserved.
  
• By allowing the use of normalized database structures (where data is split into different tables to reduce redundancy), JOINs help in efficiently managing and organizing data.
  
• JOINs are often used in conjunction with GROUP BY and aggregation functions (like COUNT, SUM, AVG) to perform comprehensive data analysis.

Syntax:

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

where,

• SELECT column_names: This identifies the columns you want to get from the tables. One can select columns in both table1 and table2.
• FROM table1: This specifies the first table to begin joining from.
• INNER JOIN table2: Identifies we wan to join table1 and table2
• ON table1.common_column = table2.common_column: This specifies the condition on which the join is based

Types of MySQL JOIN

We will Create tables and insert data to demonstrate each type of MySQL JOIN. In this case, we shall consider two tables: employees and departments.

Creating Tables

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

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

Inserting Data

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', NULL);

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

Tables:

Departments Table:

Above is the table created for performing the the queries of the JOIN

1. INNER JOIN

It Returns records that have matching values in both tables.

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

Output:

Explanation:

• The INNER JOIN query that retrieves employee names and their corresponding department names is: SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
• This joins two tables, 'employees' and 'departments', using the 'department_id' column to return only those records that have matching values in both tables.
• It gives a list of employees with the corresponding department names.

2. LEFT JOIN

It returns all records from the Left table and matched records from the Right table. If there is no match, then NULL values are returned for Right table columns.

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

Output:

3. RIGHT JOIN

It Returns all the rows from the right table and the matched rows from the left table. NULL values will be returned for columns from the left table when there are no matches.

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

Output:

Explanation:

• This RIGHT JOIN query returns all department names with corresponding names of employees working in those departments.
• It also ensures that all departments are included, even if there is no matching employee.
• If no match is found, then it results in NULL for the employee name.

4. FULL JOIN

It Returns all records when there is a match in either the left or the right table. In case of no match, NULL values are returned for columns that have no match in either table.

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

Output:

Explanation

• A LEFT JOIN result-set is combined with a RIGHT JOIN to retrieve all employee names and department names.
• The result ensures that no records will be excluded from either table.
• This query includes all employees, including those without a department, and all departments, including those without employees, filling in NULL where no match is found.

5. CROSS JOIN

It Returns the Cartesian product of two tables. Matches every row of one table with every row of another table.

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

Output:

Explanation:

• The CROSS JOIN query SELECT employees.name, departments.department_name FROM employees CROSS JOIN departments; returns a Cartesian product of the employees and departments tables, matching every employee with every department, and returning all possible combinations between employee names and department names.

6. SELF JOIN

A self join is a type of join in which a table is joined to itself. This is useful when you need to compare rows within the same table, such as relating employees to their managers in an organizational hierarchy.

Consider an employees table that needs to represent employees and their managers. To achieve this, we can introduce a new column called manager_id in the employees table to store the ID of each employee's manager. After updating the table, we'll perform a self join to associate each employee with their manager.

Step 1: Altering the Table to Add the manager_id Column

ALTER TABLE employees ADD COLUMN manager_id INT;

Step 2: Updating the Table with Manager Information

UPDATE employees SET manager_id = 3 WHERE employee_id = 1;
UPDATE employees SET manager_id = 3 WHERE employee_id = 2;
UPDATE employees SET manager_id = 4 WHERE employee_id = 3;

Output:

Step 3: Performing the Self Join

SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

Output:

Explanation:

• These queries create a manager_id column in the employees table and fill it in to establish hierarchy by designating certain employees as managers.
• The last SELECT query will self-join on the employees table to produce pairs of employees and their managers by matching manager_id with employee_id, as demonstrated through employee names alongside their managers' names.

Conclusion

In summary, MySQL JOINs provide a versatile and efficient way to retrieve and analyze data across multiple tables. By utilizing different types of JOINs, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN, and SELF JOIN, you can achieve a wide range of data retrieval scenarios, from simple to complex.