An explanation of the difference in usage between on and where in SQL statements

This blog refers to the Internet. I don’t know which article is the original manuscript, so I hereby declare it.

DatabaseWhen returning records by connecting two or more tables, an intermediate temporary table will be generated, and then this temporary table will be returned to the user.

Recommended related mysql video tutorials: "mysql tutorial"

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

1. The on condition is a condition used when generating a temporary table. It will return the records in the left table regardless of whether the condition in on is true.

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 condition is not true, all will be filtered out.

Suppose there are two tables:

Table 1:tab2

##330 Table 2: tab2

##id	size
1	10
2	20

##size##1020##CCC

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’)

name
AAA
BBB	##20
	Two SQLs:

The first one SQL process:

1. Intermediate table on condition: tab1.size = tab2.sizetab1.idtab1.sizetab2.sizetab2.name11010AAA22020BBB22020CCC330(null)(null)|| 2. Filter the where condition of the intermediate table (not available at this time True rows will be filtered out): tab2.name='AAA'tab1.idtab1.sizetab2.sizetab2.name11010AAA 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.idtab1.sizetab2.sizetab2.name11010AAA220(null)(null)330(null)(null)on is true or not, it will return

##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

left or rightRecord in the table, 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 returned result set is the same Summary:

In general, the where statement will filter out the number of rows containing NULL (the number of rows with a false condition). Therefore, in the FULL JOIN statement, the results of the where statement and the on statement are the most different; in the JOIN/INNER JOIN statement, there is no difference; in the LEFT JOIN and RIGHT JOIN, the difference depends on the expression and the data in the table.

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