Problems arising from a multi-Table query (2)

Last time I wrote the fully connected SQL statement, but did not analyze its internal process. I just briefly mentioned that four internal table scans were performed. I took the time to read it again yesterday and finally understood the principle. First, let's take a look at the figure given by the query plan:

 

Adding a nested loop to two table scans in the upper-right corner is a standard left join, which is clearly marked in the figure. The key is the two table scans and connections in the lower right corner. The table scan is to prepare for the connection, so what is the connection in the lower right corner, and we can see from the information shown in the figure, the connection type is "Left Anti Semi Join", a connection method that has never been heard. Google launched the website on the Internet. The first one found was MS's own site, which is described as follows:

Left Anti Semi Join Showplan Operator

When no matching row exists in the second (bottom) input,Left Anti Semi JoinReturns each row in the first (top) input. IfArgumentIf no join predicates exist in the column, each row is a matching row.

I thought it was a bit of a tongue twister, and I did not understand what it meant after reading the token three times. Finally, based on the query results and the results of the previous analysis, in addition, the meaning of Semi (half, not completely) found on the word overlord is probably a bit clearer. To put it bluntly, this Left Anti Semi Join is an internal operation, which is a bit of a private function. The function is to find the rows that are not displayed in the left table of the left join and appear in the right table. However, this semi is not completely understandable. It may be the result of algorithm optimization. This also complies with the definition of full join-the left table that participates in the join and the right table are not part of the result set. The rest of the concatenation is easy to understand, that is, the meaning of the union operation.
Let's look back at it. Because there are two relatively independent join operations, we have to perform four table scans.

On this basis, I thought again, since the semi-join function in it is like a right join, I will replace it with a right join operation, and then I will perform the union step myself, the internal execution process is taken out and replaced with SQL statements. Then, the following SQL statement is available:

 

1 select T1.A, T1. B, T1.C, T1.D, T2.E
2 from T1
3 left join T2 on T1.A = T2.A and T1. B = T2. B and T1.C = T2.C
4 union
5 select T1.A, T1. B, T1.C, T1.D, T2.E
6 from T2
7 left join T1 on T1.A = T2.A and T1. B = T2. B and T1.C = T2.C

 

As mentioned in the previous chapter, all right connections can be converted to an equal left connection, and the query results are completely correct. The following is a chart of the query plan for this query:

 

As shown in this figure, it is very similar to the previous figure. There are four table scans, two connections, and a concatenation operation. The second distinct has an additional sorting step, because the displayed union operation requires sorting first to exclude duplicate rows. In my opinion, the performance of the previous method may be better. In other words, the overhead of a semi-connection is at least not higher than that of a complete left connection, in addition, the sorting overhead is less than 47% after the concatenation.

The problem arising from a multi-Table query comes to an end. The query methods that can basically be thought of have also appeared here. I will add some new discoveries later.