This is a detailed tutorial of SQL SELF JOIN. Learn to JOIN a database table with itself to extract a lot of useful information from the table data.
Use of SQL SELF JOIN
You can JOIN a database table with itself using SQL SELF JOIN. Actually, you need not use any JOIN keyword for it. We just have to define two different names for the same table so that we can use those names to form different query conditions to fetch records in a useful way.
SELF JOIN Syntax
The syntax remains simple and we’ve to just define the different table names for the same table after the FROM keyword.
1 2 3 | SELECT col_1, col_2, col_3 ... FROM table_1 A, table_1 B WHERE condition; |
The above syntax will give two different names A and B to the same database table table_1. Now, we can use A and B as references for the same or different column names to form conditions.
Example
We’ll illustrate the example of SELF JOIN on our demo database table employees. The screenshot that shows the table structure and data is given below.
Now, using the SQL SELF JOIN, we’ll fetch those employees who have the same values for the column hire_date. This way we can get a very useful set of data i.e. the list of employees having the same hiring date. The query for the same is written below.
1 2 3 4 | SELECT * FROM employees E1, employees E2 WHERE E1.emp_no != E2.emp_no AND E1.hire_date = E2.hire_date ORDER BY E1.hire_date; |
We’ve given two different names E1 and E2 to the same table employees. Now, we’re adding two different conditions in the WHERE clause, one to state that emp_no should not be the same and other to state the hire_date should be the same. This way, we’re telling it to check the hire_date of each record with every other record present in the table except itself. On a very large database table, this query may take a lot of time to complete its execution.
In the result set screenshot, you can see columns are actually repeated, we’ve used the * symbol in the SELECT statement. That’s not the major concern. Check out the hire_date column values. They are in order and the employees records with the same hire_date are listed together.
This way, you can form as many as useful queries by understanding the information requirements and forming the conditions for SELF JOIN accordingly.
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Also, don’t forget to Subscribe to WTMatter!
