Easy Tutorial
❮ Verilog2 Fast2Slow Android Tutorial Sensor4 ❯

Differences Between ON and WHERE in SQL JOIN

Category Programming Technology

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:

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)
  1. 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

Cancel

-

-

-

❮ Verilog2 Fast2Slow Android Tutorial Sensor4 ❯