Home >Backend Development >PHP Tutorial >Parse the difference between using on and where to filter mysql left(right) join_PHP tutorial
There is a question about whether there is a difference in the data queried between on and where using the left(right)join filter condition in mysql in mysql query.
You may not see any problems just by looking at the two keywords. Let's use actual examples to see if there is any difference.
For example, there are two table structures
Table structure 1
Statement 2
The query result of statement 2 is:
Found a difference between the two queries.
Why there is a difference? This is related to the on and where query order.
We know that the execution order of standard query keywords is from->where->group by->having->order by[I don’t remember clearly]
left join is in the from range class, so the table is first filtered by the on condition, and then the left join is done on the two tables.
For where, the left join results are filtered again.
The query process of the first sql statement is as follows:
1: First left join
2: Filter out B.ID, that is, BID
This is the result we saw above.
The query process of the second sql statement is equivalent to:
1: First selecting the table according to the on condition is equivalent to filtering table B first:
2: Then perform a left join on the query result and table A. This is why we see that the sql of the second query retains table A.
When using ON and where, you must pay attention to the place:
(1): The filtering conditions behind ON are mainly for association tables [and for Main table brush selection conditions are not applicable].
For example
It’s quite surprising that the result is different from what we expected, and the data with AID=3 was filtered out.
But we also found that the values corresponding to AID 1 and 2 in AID and are NULL, and the associated table only takes values that meet the filtering conditions of table A.
That is, when the main table condition is after on, the attached table only takes the values that meet the main table selection conditions, and the main table is still a rounded table.
(2): The filtering conditions for the main table should be placed after where, not after ON
(3): We need to treat association tables differently. If you need to query conditions before connecting, you should put the query part
Place behind on.
If you want to filter after the connection is completed, you should place the condition after where
(4): For related tables, we can actually do a subquery first and then join
So the second sql is equivalent to