LEFT 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
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.size |
tab2.name |
|
10 |
10 |
|
2 |
20 |
20 |
bbb |
|
20 |
20 |
|
3 |
30 |
|
|
/table>
|
|
|
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)