Difference between where and on in SQL

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: tab2

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. Medium 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. filter the where Condition for the intermediate table: tab2.name = 'aaa' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

 

process of the second SQL statement: & Lt; TD width = "350" & gt; 1. on condition of the intermediate table: tab1.size = tab2.size and tab2.name = 'aaa' (records in the left table are also returned if the condition is not true.) tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA 2 20 (null) (null) 3 30 (null) (null)

the key reason for the above results is left join and right join, full join is special. records in Left or Right tables are returned no matter whether or not the conditions on are true , full has left and right. 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