SQL (on in join with where difference)

Turn:http://zzstudy.offcn.com/archives/2146LEFT JOIN: Connects all the records in the left table with the same records as the join fields in the right table.

Right join: Connects all the records in the right table with the same records as the join fields in the left table.

INNER JOIN: An inner join, also known as an equivalent connection, returns only rows in which the joined fields are equal in two tables.

Full join: Outer JOIN, returns rows from two tables: Left JOIN + RIGHT join.

Cross join: The result is a Cartesian product, that is, the number of rows in the first table multiplied by the number of rows in the second table.

Keyword: on

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left Jion, the difference between on and where conditions is 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 20 20 Bbb 2 20 20 Ccc 3 30 (NULL) (NULL) 2, then 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 also returns records from the left table) 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 the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. and inner jion does not have this particularity, the condition is placed in and where, the result set returned is the same.

SQL (on in join with where difference)