• left join: LEFT JOIN returns all records from the left table and records from the right table where the join fields are equal.
  • right join: RIGHT JOIN returns all records from the right table and records from the left table where the join fields are equal.
  • inner join: INNER JOIN, also called equi-join, returns only rows where the join fields in both tables are equal.
  • full join: OUTER JOIN, returns rows from both tables: LEFT JOIN + RIGHT JOIN.
  • cross join: The result is a Cartesian product, i.e., the number of rows in the first table multiplied by the number of rows in the second table.

Keyword ON

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

When using [LEFT JOIN]left join,onandwherethe difference between the conditions is as follows:

  • 1、 onThe [ON] condition is the condition used when generating the temporary table. Regardless ofonwhether the condition in [ON] is true, records from the left table will be returned.
  • 2、whereThe [WHERE] condition is the condition used to filter the temporary table after it has been generated. At this point, there is no longer theleft joinmeaning of [LEFT JOIN] (that it must return records from the left table); all records that do not meet the condition are 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 statements:

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')
Process of the first SQL statement:

 

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 filter the intermediate table
WHERE condition:
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

 
   

 
Process of the second SQL statement:

 

1. Intermediate table
ON condition:
tab1.size = tab2.size and tab2.name='AAA'
(Records from the left table are returned even 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 isleft join、right join、full jointhe special nature of [LEFT JOIN]: regardless ofonwhether the condition on [ON] is true, it will returnleftorrightrecords from the [LEFT] table,full[FULL JOIN] hasleftandrightthe union of the characteristics of [LEFT JOIN and RIGHT JOIN]. However,inner jion[WHERE] does not have this special nature, so whether the condition is placed inon[ON] or inwhere[WHERE], the returned result sets are the same.

Original article address: https://www.cnblogs.com/wlzhang/p/4532587.html