The difference between the in and where of SQL Server foreign joins (left join)

With an inner join, the condition has the same result in either the join clause or the WHERE clause, but not when an outer join is used.

1. When the condition is in the JOIN clause, SQL Server includes all rows for the appearance, and then uses the condition to include rows from the second table.
2. When the constraint is placed in the WHERE clause, the join is performed and the WHERE clause is applied to the join row.

The above statement is not very clear, the following explanations are clearer.

That is, the oncondition (on in the conditional expression "a left JOIN B") is used to determine how data rows are retrieved from table B.

If no row of data in table B matches the on condition, an additional row of all data that is listed as NULL will be generated.

The condition of the WHERE clause in the match phase is not used. The WHERE clause condition is used only after the match phase is complete. It retrieves the filter from the data that is produced during the matching phase.

The difference between the in and where of SQL Server foreign joins (left join)