Sql Join Table With Anotehrh Then With Itself Again
Summary: in this tutorial, you will learn how to apply the SQL Server cocky join to join a tabular array to itself.
SQL Server self join syntax
A self join allows you to join a table to itself. Information technology helps query hierarchical information or compare rows inside the same tabular array.
A self bring together uses the inner bring together or left join clause. Because the query that uses the self join references the aforementioned table, the table alias is used to assign different names to the aforementioned table within the query.
Note that referencing the same table more than one in a query without using table aliases will effect in an error.
The following shows the syntax of joining the tabular array T to itself:
SELECT select_list FROM T t1 [INNER | LEFT] Bring together T t2 ON join_predicate;
Code linguistic communication: SQL (Structured Query Linguistic communication) ( sql ) The query references the table T twice. The table aliases t1 and t2 are used to assign the T table different names in the query.
SQL Server self join examples
Permit'southward have some examples to understand how the self join works.
1) Using cocky bring together to query hierarchical data
Consider the followingstaffs tabular array from the sample database:
Thestaffs table stores the staff information such as id, first name, last proper name, and email. Information technology also has a column named manager_id that specifies the direct managing director. 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 cavalcade has a Nil.
To get who reports to whom, you use the self join as shown in the following query:
SELECT e.first_name + ' ' + eastward.last_name employee, chiliad.first_name + ' ' + m.last_name manager FROM sales.staffs e INNER Join sales.staffs m ON g.staff_id = eastward.manager_id Order By managing director;
Code language: SQL (Structured Query Language) ( sql ) 
In this example, we referenced to thestaffs table twice: one as e for the employees and the other as m for the managers. The join predicate matches employee and manager relationship using the values in the east.manager_id andm.staff_id columns.
The employee cavalcade does not have Fabiola Jackson because of the INNER JOIN effect. If you replace the INNER Bring together clause by theLEFT JOIN clause every bit shown in the following query, you will get the consequence set that includes Fabiola Jackson in the employee column:
SELECT e.first_name + ' ' + e.last_name employee, m.first_name + ' ' + m.last_name manager FROM sales.staffs due east LEFT JOIN sales.staffs chiliad ON 1000.staff_id = east.manager_id Gild Past managing director;
Code language: SQL (Structured Query Language) ( sql ) 
2) Using self join to compare rows inside a table
See the following customers table:
The following argument uses the self join to observe the customers located in the same city.
SELECT c1.urban center, c1.first_name + ' ' + c1.last_name customer_1, c2.first_name + ' ' + c2.last_name customer_2 FROM sales.customers c1 INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_id AND c1.urban center = c2.metropolis Club Past city, customer_1, customer_2;
Code linguistic communication: SQL (Structured Query Language) ( sql ) 
The following status makes sure that the statement doesn't compare the same customer:
Code language: SQL (Structured Query Language) ( sql )
c1.customer_id > c2.customer_id
And the following condition matches the urban center of the two customers:
Code language: SQL (Structured Query Language) ( sql )
AND c1.city = c2.metropolis
Note that if you change the greater than ( > ) operator by the not equal to (<>) operator, you lot volition get more than rows:
SELECT c1.city, c1.first_name + ' ' + c1.last_name customer_1, c2.first_name + ' ' + c2.last_name customer_2 FROM sales.customers c1 INNER Bring together sales.customers c2 ON c1.customer_id <> c2.customer_id AND c1.city = c2.city ORDER Past city, customer_1, customer_2;
Code language: SQL (Structured Query Linguistic communication) ( sql ) 
Let'south see the departure betwixt > and <> in the ON clause past limiting to 1 urban center to brand it easier for comparing.
The following query returns the customers located in Albany:
SELECT customer_id, first_name + ' ' + last_name c, city FROM sales.customers WHERE city = 'Albany' Society BY c;
Code language: SQL (Structured Query Language) ( sql ) 
This query uses ( >) operator in the ON clause:
SELECT c1.city, c1.first_name + ' ' + c1.last_name customer_1, c2.first_name + ' ' + c2.last_name customer_2 FROM sales.customers c1 INNER Bring together sales.customers c2 ON c1.customer_id > c2.customer_id AND c1.metropolis = c2.city WHERE c1.urban center = 'Albany' Lodge Past c1.city, customer_1, customer_2;
Code language: SQL (Structured Query Language) ( sql ) The output is:
This query uses ( <>) operator in the ON clause:
SELECT c1.metropolis, c1.first_name + ' ' + c1.last_name customer_1, c2.first_name + ' ' + c2.last_name customer_2 FROM sales.customers c1 INNER Bring together sales.customers c2 ON c1.customer_id <> c2.customer_id AND c1.urban center = c2.city WHERE c1.city = 'Albany' Lodge BY c1.city, customer_1, customer_2;
Code linguistic communication: SQL (Structured Query Language) ( sql ) Here is the output:
In this tutorial, you take learned how to use an SQL Server self join to query hierarchical information and compare rows in the same table.
Source: https://www.sqlservertutorial.net/sql-server-basics/sql-server-self-join/
0 Response to "Sql Join Table With Anotehrh Then With Itself Again"
Mag-post ng isang Komento