When a database returns records by joining two or more tables, it always generates an intermediate temporary table, and then returns this temporary table to the user.

When usingleft joinwhen,onandwhereThe difference between the conditions is as follows:

1、onThe ON condition is used when generating the temporary table; it does not careonwhether the condition in ON is true, it will return records from the left table.

2. The WHERE condition is a condition for filtering the temporary table after it has been generated. At this point, there is no longerleft jointhe meaning of LEFT JOIN (must return records from the left table); all records for which the condition is not true are filtered out.

Suppose 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 * from tab1 left join tab2 on tab1.size = tab2.size where tab2.name='AAA'
2、select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.name='AAA'

The process of the first SQL statement:

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) (null)

2. Then filter the intermediate table

where condition:

tab2.name='AAA'

tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

The process of the second SQL statement:

1. Intermediate table

on condition:

tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

In fact, the key reason for the above results isleft join,right join,full jointhe special nature of LEFT JOIN.

Regardless of whether the condition in ON is true, records from the LEFT or RIGHT table will be returned; FULL has the union of the characteristics of LEFT and RIGHT.

But INNER JOIN does not have this particularity; whether the condition is placed in ON or WHERE, the returned result set is the same.