The difference between on conditions and where conditions in SQL

When learning databases, we often encounter on and where. Today I will talk about the difference between on and where.

When the database returns records by connecting two or more tables, it will generate an intermediate temporary table, and then return this temporary table to the user.

When using left join, the difference between on and where conditions is as follows:

1. The on condition is a condition used when generating a temporary table. It does not matter whether the condition in on is true or not. will return the records in the table on the left.

2. The where condition is the condition for filtering the temporary table after the temporary table is generated. At this time, there is no meaning of left join (the records of the left table must be returned). If the conditions are not true, all will be filtered out.

Suppose there are two tables:

Table 1: tab1

id size1 102 203 30

Table 2: tab2

size name10 AAA20 BBB20 CCC

Two SQLs:

1、select * from tab1 left join tab2 on tab1.size = tab2.size where tab2.name='AAA'2、
select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.name='AAA'

The process of the first SQL:

1. Intermediate table

on condition:

tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name1 10 10 AAA2 20 20 BBB2 20 20 CCC3 30 (null) (null)

2. Then filter the intermediate table

where condition:

tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name1 10 10 AAA

The second SQL process:

1. Intermediate table

on Condition:

tab1.size = tab2.size and tab2.name='AAA'(If the condition is not true, the records in the left table will also be returned)

tab1 .id tab1.size tab2.size tab2.name1 10 10 AAA2 20 (null) (null)3 30 (null) (null)

In fact, the key reason for the above results is left join, right join , the particularity of full join.

Regardless of whether the condition on on is true or not, records in the left or right table will be returned. Full has the union of the characteristics of left and right.

The inner jion does not have this particularity, so the conditions are placed in on and where, and the result set returned is the same.

This article explains the difference between where and on. For more related knowledge, please pay attention to the php Chinese website.

Related promotion press:

About the method of compressing CSS code in PHP

PHP receives json and inserts the received data Database

PHP MySql paging example

The above is the detailed content of The difference between on conditions and where conditions in SQL. For more information, please follow other related articles on the PHP Chinese website!