The similarities and differences between left join and on,where combination

I. Database when a record is returned by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using a left join, the on and where conditions differ as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose 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: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 procedure: 1. Intermediate table on condition: Tab1.size = tab2.size tab1 . ID tab1.size tab2.size tab2.name 1 10 10 aaa 2 /td> 20 20 bbb 2 20 20 CCC 3 30 (null) (null) | | 2, again on the intermediate table filter where Condition: tab2.name= ' AAA ' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

The second SQL procedure: 1, intermediate table on condition: Tab1.size = tab2.size and tab2.name= ' AAA ' ( The condition is not true and returns the records in the left table, where the right table record is empty)   tab1 . ID tab1.size tab2.size tab2.name 1 10 10 aaa 2 /td> 20 (null) (null) /td> 3 30 (NULL) (null)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return left or Records in the right table , full has a set of attributes for left and right. The inner join does not have this particularity, the condition is placed in and where, and the result set returned is the same.

II. requirements: Show the salary level of each department according to the Department group

Expected Result:

Empid DeptID Salary Rank

1 10 5500 1

2 10 4500 2

4 20 4800 1

3 20 1900 2

7 40 44500 1

6 40 14500 2

5 40 6500 3

9 50 7500 1

8 50 6500 2

Code:

SELECT *, Row_number () over (partition by DeptID ORDER by salary desc) rank from employee

The similarities and differences between left join and on,where combination