In-depth explanation of the differences between on and where in left join of Oracle

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.