SQL Server Self Join

In this tutorial, you will learn how to use a special kind of join called SQL self Join to join a table to itself.

SQL self join is used to join or compare a table to itself. SQL self joins are used to compare values of a column with values of another column in the same table.
To construct a self join, you select from the same table twice by using the SELECT statement with an inner join or outer join clause. Because you refer to the same table twice in the same statement, you have to use table aliases. The following illustrates the syntax of a self join:

    column1, column2,...
    table AS A
    table AS B ON join_condition

he  staffs table stores the staff information such as id, first name, last name, and email. It also has a column named manager_id that specifies the direct manager. For example, Mireya reports to Fabiola because the value in the manager_id of  Mireya is Fabiola.

Fabiola has no manager so the manager id column has a NULL.

To get who reports to whom, you use the self join as shown in the following query:

    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id

Shaiv Roy

Hy Myself shaiv roy, I am a passionate blogger and love to share ideas among people, I am having good experience with laravel, vue js, react, flutter and doing website and app development work from last 7 years.

Related Articles

Leave a Reply

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

Back to top button