Shulou(Shulou.com)06/01 Report--

Xiaobian to share with you what is the difference between sql join and where, I believe most people still do not know how, so share this article for your reference, I hope you have a lot of harvest after reading this article, let's go to understand it together!

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

Recommended course: MySQL tutorial.

After join, you can follow the on condition and the where condition. Here, I mainly mean the difference between the two.

Create two simple test tables and add data, as shown below, one named id_name and the other id_age

Let's first look at the results without adding the condition--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 plus condition--t2.age ='22'.

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

The results are as follows

You can get the where condition is the conditional filter performed after the left join operation is completed

2. When running on plus 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 the condition before the left join, and then joining the two tables.

For inner join, because of its nature, the results obtained by these two conditions will be the same, but the internal process in the middle is still different.

on works earlier than where, first join multiple tables according to on condition, generate a temporary table, and then filter by where

So on and where is more efficient?

If it is an inner join, putting on and putting where produces the same result, but does not say which efficiency is faster? If there is an outer join (left or right), there is a difference, because on takes effect first, some data has been filtered in advance, and where takes effect later.

All things considered, it feels more efficient to put it in the on, because it is executed before the where.

If join is left join, then add the data in the left main table filtered by on; then perform the filtering in where; if join is left join, then add the data in the left main table filtered by on;

On is not the final filter, because left join may be added back later, and where is the final filter.

The only difference between on and where is if you use an outer join (left, right). If you use inner join, it doesn't matter where you make it, because after on is where, and there's no other step in between.

The above is sql join and where what is the difference between all the content, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to 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.