On condition clauses in Outer Join

Intranet connections are generally used in simple projects, but Chinese and foreign connections are common in actual system-level projects. In the use of external connections, I found that many people are confused about the role of the on clause with values in external connections.

When a condition is added to an internal join query, whether it is added to the join clause or the where clause, the effect is the same, but the external join condition is different. When conditions are added to the join clause, SQL Server and Informix return all rows of the Outer join table, and then return the rows of the second table using the specified conditions. If conditions are placed in the where clause, SQL Server first performs the join operation, and then uses the where clause to filter connected rows. The following two Queries show the impact of conditional placement on execution results:

The condition isJoinClause:
Select *
From t_institution I
Left outer joinT_teller t
On I. inst_no = t. inst_no
And I. inst_no = "5801"
The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david
5802 Yuexiu District
5803 Baiyun District

The condition is in the where clause:
Select *
From t_institution I
Left outer joinT_teller t
On I. inst_no = t. inst_no
Where I. inst_no = "5801"
The result is:
Inst_no inst_name inst_no teller_no teller_name
5801 Tianhe District 5801 0001 tom
5801 Tianhe District 5801 0002 david

Therefore, in an external connection, the attribute in on is equal to the condition of a specific value. When the attribute involved isMaster tableIn fact, this condition does not play a screening role.

Alternatively, you can use a method to precisely express the attributes in the primary table filtered out by the on clause in case of external connections and on conditions. (If you do not understand, read another article: SQL statement execution sequence)