The difference between on and where the filter is placed in the left outer join in SQL

The difference between on and where the filter is placed in the left outer join in SQL

CREATE TABLE [Table_1] (
[PKey] int
, [Fkey] int
, [value1] int
, [value2] int
)
Create table[table_2]
([PKey] int
, [value1] int
, [value2] int
)
drop table [Table_1]
drop table [table_2]
Delete[table_1]
Delete[table_2]
insert into [table_1] values (1,0,21,31)
insert into [table_1] values (2,0,22,31)
insert into [table_1] values (3,0,23,31)
insert into [table_1] values (4,0,24,31)
insert into [table_1] values (5,0,21,31)--duplicate 21
insert into [table_1] values (6,0,25,31)--no-in table2
insert into [table_1] values (5,1,21,31)--condition table1 Fkey =1

insert into [table_2] values (6,21,32)
insert into [table_2] values (7,22,32)
insert into [table_2] VALUES (8,23,33)--seeing on and where difference
insert into [table_2] values (9,24,32)
insert into [table_2] VALUES (10,26,32) – No in Table1
SELECT * FROM [Table_1]
SELECT * FROM [table_2]
----Select t1.*, ' | ' n,t2.* from table_1 t1
----INNER JOIN table_2 T2 on t1.value1 =t2.value1
--left Outer JOIN Learning
Select t1.*, ' | ' n,t2.* from table_1 t1
Left outer join table_2 T2 on t1.value1 =t2.value1 order by t1.value1
--part1:
Select t1.*, ' | ' n,t2.* from table_2 T2
Left outer joins Table_1 T1 on T1.value1 =t2.value1
ORDER BY T1.value1

--part2:

Select t1.*, ' | ' n,t2.* from table_2 T2
Left outer joins Table_1 T1 on T1.value1 =t2.value1
where T1.fkey = 0
ORDER BY T1.value1

--PART3:

Select t1.*, ' | ' n,t2.* from table_2 T2
Left outer join table_1 t1 on t1.fkey = 0
and T1.value1 =t2.value1
ORDER BY T1.value1

Left JOIN Result:

Own understanding of:

Part1 vs Part2:join, where filters are made to the result set, so the end result will not have a row of T1.fkey = 0.

Part1 vs Part3:join, T1.fkey = 0 is filtered to Table_1 to get the virtual table_1_2, and then Table_1_2 joins with Table2.

Based on theory:

What does T-SQL do in querying each class:

(1) from stage

The from stage identifies the source table of the query and processes the table operator. In queries that involve join operations (various joins), there are several main steps:

A. Seek Cartesian product. Regardless of the type of join operation, the first is to perform a cross join, to seek Cartesian product, to generate virtual table vt1-j1.

B.on filter. This phase filters the vt1-j1 generated from the previous step, filtering the predicate that appears in the ON clause, and the line that evaluates to TRUE for the predicate is tested and inserted into VT1-J2.

C. Add an external row. If you specify a outer join, you also need to add the vt1-j2 in the vt1-j2, as an outer row, to generate the vt1-j3 if no matching rows are found.

After the above steps, the from phase is complete. In a nutshell, the from phase is preprocessed, and the tables mentioned in the statement are processed according to the provided operator (with the exception of join and Apply,pivot,unpivot)

(2) where phase

The Where stage filters the rows in the VT1 based on the conditions in the <where_predicate>, allowing the rows to be set to be inserted into the VT2.

(3) GROUP by stage

The group stage groups the rows in the VT2 according to the specified list of column names, generating VT3. Finally, each group has only one row.

(4) The having stage

This stage filters the grouping of VT3 based on the predicates that appear in the HAVING clause and inserts the eligible groups into the VT4.

(5) Select stage

This stage is the projection process that processes the elements mentioned in the SELECT clause, producing VT5. This step is usually done in the following order

A. Calculate the expression in the select list to generate Vt5-1.

B. If you have distinct, delete the duplicate rows in the vt5-1 and generate the Vt5-2

C. If there is a top, select the line from vt5-2 that specifies the number or percentage of the signature, based on the logical order defined by the ORDER BY clause, to generate the vt5-3

(6) ORDER by stage

Sorts the rows in vt5-3 according to the list of columns specified in the ORDER BY clause, generating a cursor VC6.

Copy a picture that someone forwarded. The following is an illustration of the SQL execution order given in the <<inside Microsoft SQL Server querying>> T-SQL book.

The difference between on and where the filter is placed in the left outer join in SQL