You use self-join to create a result set that joins the rows with the other rows within the same table. Because you cannot refer to the same table more than one in a query, you need to use a table alias to assign the table a different name when you use self-join. The self-join compares values of the same or different columns in the same table. Only one table is involved in the self-join. We will use the employees table in the sample database for demonstration.
This type of data structure is called a tree structure, and you often need to join the table with itself in SQL. Here are some examples. Each employee has a manager, and a manager in turn has his or her manager, all in the same table. If you want to add the corresponding manager information to each record, you need to do a self join. Each department within an organization has a parent: for example, the Data Science department is under the IT department, the IT department is under Business Support, and Business Support is under the board.
Inside this SELECT , we join the department table with itself to get the parent department name as an additional field. This is because the board of directors are at the top of the tree structure. You can easily see the parent to which each department belongs: IT is under Administration, Administration is under the Board, etc. Take classified advertising—those popular web pages where you can rent, buy, or sell anything from real estate to miscellaneous products and services.
To place an ad, you pick a category and a subcategory for your ad. For example, if you sell real estate, you would choose among subcategories like house, apartment, or land. We have a table named category which contains information about these categories and subcategories as well as their relationships.
The relationships among the categories and subcategories in this table are stored in a parent-child structure like this:. All categories and subcategories are in this one table. Now, If you want to add information about the parent to each record, you will need to do a self join—join this table to itself:. There are situations beside the self join in which you need to join the same table more than once.
One is when you have multiple relationships between two different tables. This is where you join the same table twice but usually to some other table and not necessarily to itself.
Suppose that the customer table has two fields that contain city IDs. This is common if you have two different cities for each customer? Now, if you want to display the names of the cities, you will have to join the city table twice:. We use the aliases to define the columns in the resulting table.
Aliases are also used during the join to define the key columns. Again, aliases are required in order to distinguish the two copies of city. We use simple i. If you prefer to learn by watching videos. The column ParentCategoryId from the table aliased category is matched with Id from the table aliased parentcategory.
The ON clause specifies that ParentCategoryId from category must equal Id from parentcategory to connect the corresponding rows. You can use a self join to generate pairs of rows based on the condition in the ON clause. Consider the following table, Colleagues :. Suppose we need to generate all possible pairs among the colleagues so that everyone has a chance to talk with everyone else at the company introductory evening. The result matches every person with every single person in the table.
FullName teammate2. This means that each person will be paired with three other colleagues, because there are four colleagues at this event. Consider the Human table shown below. We want to match all the ancestors to each person wherever the data allows, where a person is an ancestor if he or she has a higher Id value.
By specifying the ON clause condition descendant. ParentId , we find all the ancestors of every person in the table where they exist; otherwise, the query returns null for the ancestor information. The first four columns are taken from the table aliased descendant which contains information of the person for whom the ancestors are searched.
The last three columns are taken from the table aliased ancestor and contain details about each ancestor. In SQL, it is possible to have a self join in combination with one or more different tables. While not a clean self join, this is very common in practice. A real-life example of this is the flight information in airports, with an enormous amount of data each hour. Suppose we want to search for a flight identification number along with the details about their departure and destination airports.
Consider the following tables:. We will join the Airport table to the Flight table two separate times as follows:. The next two columns come from Airport in the role of the starting airport table; the rows are matched based on AirportId and StartAirportId from the Airport and Flight tables, respectively.
This is followed by a column from the Flight table. The last two columns come from Airport in the role of the destination airport table this time; the rows are matched based on AirportId and EndAirportId from the Airport and Flight tables, respectively.
Still a bit confused about all the JOIN s? There are many more articles for you to browse through for help.
0コメント