Shulou(Shulou.com)06/01 Report--

Today, we come across a statistical result of finding all the days of a month. If the result of a certain day is 0, it also needs to be displayed, that is:

Date number of transactions transaction amount

2009-4-01 1 10

2009-4-02 2 20

2009-4-03 00

2009-4-04 5 50

At first, I used the left join, used on as the association condition of the two tables, and used where as the filter condition, but found that the data of 0 was not displayed at all. Later, I removed the where keyword and put all the filter conditions into on. The problem was solved and a search on the Internet found the answer:

When a database returns a record by joining two or more tables, it generates an intermediate temporary table, and then returns the temporary table to the user.

When using left jion, the differences between on and where conditions are as follows:

1. The on condition is the condition used when generating the temporary table, which returns the record in the left table regardless of whether the condition in on is true or not.

2. The where condition is the condition that the temporary table is filtered after the temporary table is generated. At this point, there is no meaning of left join (the record of the table on the left must be returned). If the condition is not true, it will be filtered out.

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

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 again

Where condition:

Tab2.name='AAA'

Tab1.id tab1.size tab2.size tab2.name

1 10 10 AAA

The second process of SQL:

1. Intermediate table

On condition:

Tab1.size = tab2.size and tab2.name='AAA'

(the record in the left table will 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,full join. Records in left or right tables are returned regardless of whether the condition on on is true or not, while full has the union of the characteristics of left and right. While inner jion does not have this particularity, the condition is placed in on and where, and the returned result set is phase.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.