[go: up one dir, main page]
Scribd
Upload
41 views5 pages

DBMS LAB # 9 Self-Join

This document explains the concept of self-joins in SQL Server, which allows a table to join with itself for querying hierarchical data or comparing rows. It provides syntax for self-joins and examples demonstrating how to use INNER JOIN and LEFT JOIN to retrieve employee-manager relationships and compare customers in the same city. Additionally, it includes a task for users to create a sample database and apply self-join queries in different scenarios.

Uploaded by

sunbornmarketingagency
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
41 views5 pages

DBMS LAB # 9 Self-Join

This document explains the concept of self-joins in SQL Server, which allows a table to join with itself for querying hierarchical data or comparing rows. It provides syntax for self-joins and examples demonstrating how to use INNER JOIN and LEFT JOIN to retrieve employee-manager relationships and compare customers in the same city. Additionally, it includes a task for users to create a sample database and apply self-join queries in different scenarios.

Uploaded by

sunbornmarketingagency
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 5

DBMS LAB # 09

SELF-JOIN
LAB # 9 (Self-Join)

SQL Server self-join


A self join allows you to join a table to itself. It is useful for querying hierarchical data or
comparing rows within the same table.

A self join uses the inner join or left join clause. Because the query that uses self join references
the same table, the table alias is used to assign different names to the table within the query.

Note that if you reference the same table more than once in a query without using table aliases,
you will get an error.

Syntax:
SELECT
select_list
FROM
T t1
[INNER | LEFT] JOIN T t2 ON
join_predicate;

SQL Server self-join examples


Let’s take some examples to understand the how the self-join works.

1) Using self-join to query hierarchical data


Consider the following staffs table from the sample database:
The 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.
To get who reports to whom, you use the self-join as shown in the following query:

1 SELECT
2 e.first_name + ' ' + e.last_name employee,
3 m.first_name + ' ' + m.last_name manager
4 FROM
5 sales.staffs e
6 INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
7 ORDER BY
8 manager;

The employee column does not have Fabiola Jackson because of the INNER JOIN effect. If you
replace the INNER JOIN clause by the LEFT JOIN clause as shown in the following query, you
will get the result set that includes Fabiola Jackson in the employee column:
1 SELECT
2 e.first_name + ' ' + e.last_name employee,
3 m.first_name + ' ' + m.last_name manager
4 FROM
5 sales.staffs e
6 LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
7 ORDER BY
8 manager;
2) Using self-join to compare rows within a table
The following statement uses the self join to find the customers located in the same city.

1
2 SELECT
3 c1.first_name + ' ' + c1.last_name customer_1,
4 c2.first_name + ' ' + c2.last_name customer_2,
5 c1.city
6 FROM
7 sales.customers c1
8 INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
9 AND c1.city = c2.city
1 ORDER BY
0 customer_1,
1 customer_2;
1

Task:
Step 1: Create the above-mentioned sample database on your machine and populate the tables.
Step 2: Apply at least four queries of self-join on four different scenarios.

Link to Sample Database: https://www.sqlservertutorial.net/sql-server-sample-database/


References:
https://www.javatpoint.com/database-schema

You might also like