Today, we have a statistical result for all days of a month. If the result of a day is 0, it must be displayed, that is:
Date transaction count transaction amount
2009-4-01 1 10
2009-4-0
2009-4-03 0 0
....
At first, I used the left join, on as the join condition for two tables, and where as the filter condition. However, I found that the data with 0 is not displayed, and then I removed the where keyword, put all the filtering conditions in on, solve the problem, search online, and find the answer:
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 are returned no matter whether the conditions on the on are true, 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.