Differences Between ON and WHERE in SQL JOIN
Category Programming Technology
left join
: Left join returns all records from the left table and the matched records from the right table.right join
: Right join returns all records from the right table and the matched records from the left table.inner join
: Inner join, also known as equi-join, returns only the rows where there is a match in both tables.full join
: Full join returns all records when there is a match in either the left or the right table.cross join
: The result is the Cartesian product, which is the number of rows in the first table multiplied by the number of rows in the second table.
Keyword ON
When the database connects two or more tables to return records, it generates an intermediate temporary table, which is then returned to the user.
When using left join
, the differences between on and where conditions are as follows:
- on conditions are used when generating the temporary table. It returns all records from the left table regardless of whether the condition in on is true.
- where conditions are applied after the temporary table is generated, filtering the temporary table. At this point, the meaning of
left join
(which must return records from the left table) is no longer applicable, and any records that do not meet the condition are filtered out.
- where conditions are applied after the temporary table is generated, filtering the temporary table. At this point, the meaning of
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 queries:
select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')
| First SQL query 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) |
- Filter the intermediate table with where condition: tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name 1 10 10 AAA
| Second SQL query process: 1. Intermediate table on condition: tab1.size = tab2.size and tab2.name='AAA' (even if the condition is not true, it 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) |
The key reason for the above results is the specificity of left join
, right join
, and full join
. Regardless of whether the condition on on is true, these joins return records from the left or right table. full join
combines the characteristics of both left
and right
joins. inner join
does not have this specificity, so the condition placed in on or where returns the same result set.
Original Source: https://www.cnblogs.com/wlzhang/p/4532587.html
** Click to Share Your Notes
-
-
-