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:
employee_id | name | department_id |
|---|
1 | Alice | 1 |
|---|
2 | Bob | 2 |
|---|
3 | Charlie | 1 |
|---|
4 | David | 3 |
|---|
5 | Eve | NULL |
|---|
Departments Table:
department_id | department_name |
|---|
1 | HR |
|---|
2 | Engineering |
|---|
3 | Marketing |
|---|
4 | Finance |
|---|
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:
name | department_name |
|---|
Alice | HR |
Bob | Engineering |
Charlie | HR |
David | Marketing |
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:
name | department_name |
|---|
Alice | HR |
|---|
Bob | Engineering |
|---|
Charlie | HR |
|---|
David | Marketing |
|---|
Eve | NULL |
|---|
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:
name | department_name |
|---|
Alice | HR |
Bob | Engineering |
Charlie | HR |
David | Marketing |
NULL | Finance |
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:
name | department_name |
|---|
Alice | HR |
|---|
Bob | Engineering |
|---|
Charlie | HR |
|---|
David | Marketing |
|---|
Eve | NULL |
|---|
NULL | Finance |
|---|
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:
name | department_name |
|---|
Alice | HR |
Alice | Engineering |
Alice | Marketing |
Alice | Finance |
Bob | HR |
Bob | Engineering |
Bob | Marketing |
Bob | Finance |
Charlie | HR |
Charlie | Engineering |
Charlie | Marketing |
Charlie | Finance |
David | HR |
David | Engineering |
David | Marketing |
David | Finance |
Eve | HR |
Eve | Engineering |
Eve | Marketing |
Eve | Finance |
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;
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:
| employee_id | name |
|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
SELECT a.name AS employee, b.name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
Output:
employee | manager |
|---|
Alice | Charlie |
Bob | Charlie |
Charlie | David |
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.
Similar Reads
MySQL LEFT JOIN In databases, data is often stored in multiple tables, making it necessary to combine them to fetch required information. MySQL JOIN statements enable merging tables based on common columns. In this article, we'll explore the MySQL LEFT JOIN keyword, a type of outer join that returns all records fro
5 min read
MySQL SELF JOIN Joins are very important for effective data retrieval and analysis. The 'JOIN' clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and SELF JOIN. In this ar
5 min read
MySQL RIGHT JOIN In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, joins enable the merging of multiple tables based on the common columns. In this article, we are going to explore MySQL RIGHT JOINS which is a type
5 min read
MySQL Outer Join MySQL is a popular and strong system that is extensively employed for handling data in the field of relational databases. A crucial aspect of working with databases is the ability to retrieve and handle data from different tables. SQL joins combine rows from different tables by connecting them based
4 min read
MySQL CROSS JOIN MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL
5 min read