Detailed explanation of the difference between on and where in left and join of Oracle

  • 2021-08-31 09:35:07
  • OfStack

Today, I encountered a statistical result of seeking all days in a month. If the result of a certain day is 0, it also needs to be displayed, that is:
Date, number of transactions, amount of transactions
2009-4-01 1 10
2009-4-02 2 20
2009-4-03 0 0
2009-4-04 5 50
....

1 I started with the left connection, with on as two table association conditions, with where as a filter condition, but found that the data of 0 is not displayed at all, and then removed the keyword where, put the filter conditions into on, solve the problem, search on the Internet, and find the answer:
When a database returns records by concatenating two or more tables, it generates a temporary table in the middle, which is then returned to the user.

When using left jion, the on and where conditions differ as follows:
1. The on condition is a condition used when generating a temporary table, which returns the records in the left table regardless of whether the condition in on is true or not.
2. where condition is the condition that filters the temporary table after it is generated. At this time, there is no meaning of left join (the record in the left table must be returned), and all the conditions are filtered out if they are not true.

Suppose you have 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:
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')
Article 1 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 again
where conditions:
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

Article 2 Process of SQL:
1. Intermediate table
on Condition:
tab1.size = tab2.size and tab2.name='AAA'
(If the condition is not true, the record in the left table will be returned.)
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, full join. No matter whether the conditions on on are true or not, the records in left or right tables will be returned, and full has the union of left and right characteristics. While inner jion does not have this particularity, the condition is placed in on and where, and the return result set is the same.


Related articles: