The difference between join and where in sql

The function of the join keyword is to join multiple tables according to certain conditions, so that data can be obtained from multiple tables.

Recommended courses: MySQL Tutorial.

The on condition and where condition can be followed by join. Here I mainly talk about the difference between the two

Create two simple tables for testing and add data, as shown below, one table is named id_name, the other is named id_age

First look at the result without adding conditions - t2.age ='22'

SELECT * from id_name t1 LEFT JOIN id_age t2 on t1.id = t2.id

Get

1. Take left join as an example here. First run where and add the condition - when t2.age ='22'

SELECT * from id_name t1 LEFT JOIN id_age t2 on t1.id = t2.id where t2.age ='22'

The result is as follows

The where condition can be obtained by filtering the conditions after the left join operation is completed.

2. When running on to add the condition - t2.age = '22'

SELECT * from id_name t1 LEFT JOIN id_age t2 on t1.id = t2.id and t2.age ='22'

The results are as follows

The on condition can be obtained by filtering conditions before left join, and then joining the two tables.

Here, left join is used as For example, for inner join, due to its nature, the results obtained by the two conditions will be the same, but the internal process is still different.

on works earlier than where, first proceed according to the on condition For multi-table join operations, generate a temporary table and then filter by where

Which on and where is more efficient?

If it is an inner join, put on and put Where produces the same result, but it doesn't say which one is more efficient and faster? If there is an outer join (left or right), there will be a difference, because on takes effect first, and some data has been filtered in advance, while where takes effect later.

To sum up, it feels like it is more efficient to put it in on, because it is executed before where.
First Cartesian product, then on filtering, if join is inner, continue going down. If the join is a left join, add back the data in the left main table filtered out by on; then perform the filtering in where;

on is not the final filtering, because the left join may add it back later. , and where is the final filtering.

Only when using outer join (left, right), there is this difference between on and where. If using inner join, it will be the same wherever it is formulated, because after on is where , there are no other steps in between.

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