Shulou(Shulou.com)05/31 Report--

This article mainly introduces the difference between on and where in SQL, which is very detailed and has certain reference value. Friends who are interested must read it!

The join process can be understood as follows: first, two tables make a Cartesian product. The condition behind on is that the Cartesian product is filtered to form a temporary table. If there is no where, the result is returned directly. If there is a where, filter the temporary table in the previous step. Let's look at the experiment:

Prepare two tables first:

Execute inner join first:

Select * from person p inner join account an on p.id=a.id and p.identices4 and a.identices4

Select * from person p inner join account an on p.id=a.id where p.identices4 and a.identices4

There is no difference in the result. The former is to first calculate the Cartesian product and then filter according to the conditions behind on, while the latter is to filter first with the conditions behind on and then with the conditions of where.

And look at the left link left join.

Select * from person p left join account an on p.id=a.id and p.identices4 and a.identices4

Now we can see that the record with an id of 4 is still there, which is determined by the characteristics of left join. When using left join, the condition behind on is only valid for the right table (you can see that the record of the id=4 in the right table is gone)

Select * from person p left join account an on p.id=a.id where p.identices4 and a.identices4

The filtering effect of where comes out.

The principle of right connection is the same.

At this point, it is clear that there is no difference between on and where in inner join, but there is a difference between the right connection and the left connection.

The above is all the contents of this article entitled "what are the differences between on and where in filtering conditions in SQL?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.