In this tutorial, you have learned how to use an SQL Server self join to query hierarchical data and compare rows in the same table. This query uses ( ) operator in the ON clause: SELECT This query uses ( >) operator in the ON clause: SELECT
#Sqlite inner join syntax code#
The following query returns the customers located in Albany: SELECTĬustomer_id, first_name + ' ' + last_name c,Ĭ Code language: SQL (Structured Query Language) ( sql ) Let’s see the difference between > and in the ON clause by limiting to one city to make it easier for comparison. INNER JOIN sales.customers c2 ON c1.customer_id c2.customer_id Note that if you change the greater than ( > ) operator by the not equal to () operator, you will get more rows: SELECT The following condition makes sure that the statement doesn’t compare the same customer: c1.customer_id > c2.customer_id Code language: SQL (Structured Query Language) ( sql )Īnd the following condition matches the city of the two customers: AND c1.city = c2.city Code language: SQL (Structured Query Language) ( sql ) INNER JOIN sales.customers c2 ON c1.customer_id > c2.customer_idĬustomer_2 Code language: SQL (Structured Query Language) ( sql ) SELECTĬ1.first_name + ' ' + c1.last_name customer_1,Ĭ2.first_name + ' ' + c2.last_name customer_2 The following statement uses the self join to find the customers located in the same city.
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_idĬode language: SQL (Structured Query Language) ( sql ) 2) Using self join to compare rows within a table 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: SELECT The employee column does not have Fabiola Jackson because of the INNER JOIN effect. The join predicate matches employee and manager relationship using the values in the e.manager_id and m.staff_id columns. In this example, we referenced to the staffs table twice: one as e for the employees and the other as m for the managers. INNER JOIN sales.staffs m ON m.staff_id = e.manager_id Table aliases are used to assign different names to the same table within the query. To get who reports to whom, you use the self join as shown in the following query: SELECTĮ.first_name + ' ' + e.last_name employee, It uses either the inner join or left outer join syntax. For example, Mireya reports to Fabiola because the value in the manager_id of Mireya is Fabiola.įabiola has no manager, so the manager id column has a NULL. It also has a column named manager_id that specifies the direct manager. The staffs table stores the staff information such as id, first name, last name, and email. 1) Using self join to query hierarchical dataĬonsider the following staffs table from the sample database: Let’s take some examples to understand how the self join works. The table aliases t1 and t2 are used to assign the T table different names in the query. The following shows the syntax of joining the table T to itself: SELECTĬode language: SQL (Structured Query Language) ( sql ) Note that referencing the same table more than one in a query without using table aliases will result in an error.
0 kommentar(er)
