Confusion about left join and where Condition

Left join confusions: Once the where condition is added, the result displayed is equal to inner join.

Replace where with and

Use Where to connect first and then Filter
Use and to filter and connect

When the Database connects two or more tables to return records, it will generate a temporary table in the middle and then return this temporary table to the user.

When left jion is used, the on and where conditions are different as follows:

1. The On condition is used to generate a temporary table. It returns records in the left table no matter whether the on condition is true or not.

2. The where condition is used to filter the temporary table after the temporary table is generated. At this time, there is no meaning of left join (records in the left table must be returned). If the condition is not true, all records are filtered out.

Assume there are two tables:

Table 1 tab1:

Id size

1 10

2 20

3 30

Table 2 tab2:

Size name

10 aaa

20 bbb

20 ccc

Two SQL statements:
1. Select * Form tab1 left join tab2 on (tab1.size = tab2.size) Where tab2.name = 'aaa'
2. Select * Form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name = 'aaa ')

The first SQL process:

1. Intermediate table
On condition:
Tab1.size = tab2.size

Tab1.id tab1.size tab2.size tab2.name

1 10 10 aaa

2 20 20 bbb

2 20 20 ccc

3 30 (null)

2. filter the intermediate table.
Where condition:
Tab2.name = 'aaa'

Tab1.id tab1.size tab2.size tab2.name

1 10 10 aaa

The second SQL process:

1. Intermediate table
On condition:
Tab1.size = tab2.size and tab2.name = 'aaa'
(Records in the left table will also be returned if the condition is not true)

Tab1.id tab1.size tab2.size tab2.name

1 10 10 aaa

2 20 (null)

3 30 (null)

in fact, the key reason for the above results is the particularity of left join, right join, and full join. Records in the Left or Right table will be returned no matter whether the conditions on are true or not, full is the union of left and right features. Inner jion does not have this particularity, so the condition is placed in the on and where, and the returned result set is the same.