SQL SELF JOIN

SQL SELF JOIN

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.

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.

Demo Database Table Employees For SQL SELF JOIN Example

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.

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.

SQL SELF JOIN Example

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!

Subscribe To WTMatter!

Receive updates of our latest articles via email. Enter your email address below to get started.

Leave a Reply

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