In our day-to-day life, we keep our data in various logical tables in the relational database connected through a common key value. As a result, we may need to filter out some data values to get the desired result based on some conditions. And that’s where the JOIN clause comes into the picture. In this tutorial, we will learn a special type of join, which combines the same table twice and also joins the table to itself.
What are JOINS?
Joins are used to combine data from multiple tables and fetch the records from these tables based on the presence of a common column between them.
Example of JOINS in MySQL
Suppose we have two tables, named employeees and departments, and we can combine them using a common column between them, here, it is department_id.
So, to combine both tables, we can do it like this:
SELECT employeees.employee_id, employeees.employee_name, departments.department_name
FROM employeees
JOIN departments ON employeees.department_id = departments.department_id;Code language: SQL (Structured Query Language) (sql)
In the above table, we can see that we have retrieved four columns from employeees table and one from the departments table. There are several types of Joins in MySQL. In this example, we have used INNER JOIN.
NOTE: To know more about the INNER JOIN, click here!
Join the Same Table Multiple Times
Often, we need to join the same table multiple times. Generally, it includes one or more columns to a result set from the same database but distinct records.
We will look at two different scenarios:
- Joining a Table to Itself
- Joining Tables with Multiple Relationships
1. Joining a Table to Itself (SELF JOIN)
Self-join is a type of relational database join operation that joins the table with itself and matches those records that have common values in both tables. In a Self-join, records from the same table are matched based on some unique values in different columns within the same table.
Syntax:
SELECT t1.cols1, t1.cols2, ..., t2.cols1, t2.cols2, ...
FROM table_name t1
JOIN table_name t2 ON t1.related_cols = t2.related_cols;
table_name: Name of the table you want to join.
t1,t2: Aliases for the table_name.
related_cols: Columns that you use ,to join to itself.Code language: SQL (Structured Query Language) (sql)So, in our example above, the “manager_id” column refers to another employee who is the manager of the current employee. The “manager_id” column can be NULL if the employee doesn’t have a manager now if we want to fetch the employees’ names with their managers’ names. We can do this using a self-join on the “employeees” table.
Example:
SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
FROM employeees e
LEFT JOIN employeees m ON e.manager_id = m.employee_id;Code language: SQL (Structured Query Language) (sql)
2. Joining Tables with Multiple Relationships
In certain situations, we require the same table to join multiple times. It includes specifying numerous join conditions to determine the connections between the tables.
Example:
So, in our example above, we know that we have two tables named ’employeees’ and ‘departments’, and there are two relationships between them. One is that the employees are managed by other employees (who also belong to other departments), and the other is that the employees belong to a particular department. So, to combine them:
SELECT
e.employee_id,
e.employee_name,
d.department_name AS employee_department,
m.employee_name AS manager_name,
md.department_name AS manager_department
FROM
employeees e
JOIN
departments d ON e.department_id = d.department_id
LEFT JOIN
employees m ON e.manager_id = m.employee_id
LEFT JOIN
departments md ON m.department_id = md.department_id;Code language: SQL (Structured Query Language) (sql)
Let’s break down what is happening in the above example. Firstly, the employeees table is joined with the departments table to connect each employee with their particular department based on the common ‘department_id’ column. Then, LEFT JOIN connects each employee with their specific manager using the ‘manager id’ column in the ’employeees’ table to match with the ’employee_id’ of the manager. Lastly, LEFT JOIN is used to connect the managers with their respective departments using ‘department-id’ column.
Conclusion
In this tutorial, we have learned when and how to use the Self-join and the various conditions where we can join the same table multiple times to access the relationship between the entities. If you want to learn about joining multiple tables, click here!
Reference
https://stackoverflow.com/questions/199953/how-do-you-join-on-the-same-table-twice-in-mysql