Self Join

How To Use Self Join In SQL

Introduction:
In this article, you will learn, what Self Join is and how to use Self Join in SQL.

Self Join, joins at least one table automatically. If we use Self Join, we create two tables of the same name. Self join is beneficial, when it has many join tables in SQL. The result of self join is same in the two tables. The SQL Self Query collects all the data, weather it is duplicate or not (according to our condition).

Use a self-join, when you want to create a result set, which joins the records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name. This table helps the query processor to determine whether columns should present the data from the right or left version of the table.

Here, we have two tables -

Employee Table

Now, we collect the same records in both the tables, according to our condition.

Syntax

The syntax of Self Join is as follows.

select a.column_name, b.column_name... from table1 a, table1 b where a.common_field = b.common_field;

Example

Step 1: We write our query for Self Join.

Select a.Id, a.Name, phone_no, b.Amount from student a, query b where a.salary < b. Amount;

Now, select and execute this query.

Step 2: The output of this query is:

Summary:
Thus, we learned, Self Join generates the table automatically.