Difference between SQL on and where, difference between SQLonwhere

Difference between SQL on and where, difference between SQLonwhere

This blog has been posted on the Internet. I do not know which one is the source.

 

DatabaseWhen two or more tables are connected to return records, a temporary table in the middle is generated, and then this temporary table is returned 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. 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. filter the intermediate table. Where condition (in this case, it is not filtered out for the real row ): 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) (Null) 3 30 (Null) (Null)

In fact, the key reason for the above results is the particularity of left join, right join, and full join,No matterOnReturns true or not.LeftOrRightRecords in the table, Full indicates 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

Summary:

In general, the where statement filters out the number of rows containing NULL (the condition is false ). Therefore, the results of the where statement and the on statement are the most different in the full join statement. There is no difference in the JOIN/inner join statement; the difference between left join and right join depends on the expression and the data in the table.