The reason for writing this article is that during the interview, the interviewer asked me what the difference is between a left join b and b right join a. I said there is no difference. The query results are the same, and the performance is the same. The interviewer said that the sequence of small tables and large tables has an impact on the performance. He said that sql statements are executed from left to right. If there is a large table on the left, It will scan the data of the table on the left first, and then the data of the table on the right. What a mistake! Let's hit him in the face with practice!

I will practice SqlServer and MySql respectively, because the parsing mechanisms of the two databases in Sql are still very different.

SqlServer:

tEBay_Trade = "data volume 500W" tpublic_ Shop = > data volume 30

-----If the where condition hits an index and there is only one index
 --For join, whether large tables or small tables are in the first place, the table where the index is located is always the first scanned table in the query order

select * from tPublic_Shop s
join tEBay_Trade t on s.id=t.ShopID
where s.Id>10

 

select * from tPublic_Shop s
join tEBay_Trade t on s.id=t.ShopID
where t.CreateDate>'2021-01-01'

--For left join and right join, the table where the conditional index is located will always be the first scan table

select * from tEBay_Trade t
left join tPublic_Shop s on s.id=t.ShopID
where t.CreateDate>'2021-01-01'

 

 

select * from tPublic_Shop s
left join tEBay_Trade t on s.id=t.ShopID
where t.Id>10

 

select * from tPublic_Shop s
right join tEBay_Trade t on s.id=t.ShopID
where t.CreateDate>'2021-01-01'

-----If the where condition hits the index and there are multiple indexes
 --For join, no matter whether the large table is in the front or the small table is in the back, if multiple where conditions hit the index, the largest table corresponding to the index will be the first scan table

select * from tPublic_Shop s
join tEBay_Trade t on s.id=t.ShopID
where s.Id>10 and t.CreateDate>'2021-01-01'

 

select * from tEBay_Trade t
join tPublic_Shop s on s.id=t.ShopID
where t.CreateDate>'2021-01-01' and s.Id>10

 

 

-----If there is no index or the where condition misses the index
 --join if there is no index or the where condition misses the index, the system will automatically select the small table as the first scan table

select * from tPublic_Shop s
join tEBay_Trade t on s.id=t.ShopID

 

select * from tEBay_Trade t
join tPublic_Shop s on s.id=t.ShopID

--left join and right join if there is no index or the where condition misses the index, the system will automatically select the small table as the first scan table

select * from tEBay_Trade t
left join tPublic_Shop s on s.id=t.ShopID

 

select * from tPublic_Shop s
left join tEBay_Trade t on s.id=t.ShopID

 

 

select * from tPublic_Shop s
right join tEBay_Trade t on s.id=t.ShopID

MySql is very different. The difference between formal and wild products is obvious:

tuser data volume: 33954; torder data volume: 4612

#As for join, whether the index is added to the main table or not, always scan the smallest table first

select * from tuser u
join torder t on u.id=t.uid
where u.id>500

select * from tuser u
join torder t on u.id=t.uid
where t.createtime>='2021-07-01'

select * from torder t
join tuser u on u.id=t.uid
where u.id>500

#Whether it is a left join or a right join, if there is no conditional hit index, the main table is the first scanned table

select * from torder t
left join tuser u on u.id=t.uid

select * from tuser u
right join torder t on u.id=t.uid

select * from tuser u
left join torder t on u.id=t.uid

 

 

#Whether it is left join or right left, if the where condition hits the index, the table corresponding to this index will be the first scanned table

select * from torder t
right join tuser u on u.id=t.uid
where t.createtime>='2021-07-01'

select * from torder t
right join tuser u on u.id=t.uid
where u.id>500

select * from tuser u
left join torder t on u.id=t.uid
where t.createtime>='2021-07-01'

select * from tuser u
left join torder t on u.id=t.uid
where u.id>500

 

Summary:

About SqlServer:

1. For join, left join and right join, no matter whether the large table or the small table is in front, if the where condition hits the index, the table in which the where condition index is located will always be the first scanned table

2. For join, left join and right join, whether the large table or the small table is in front, if the where condition hits the index and there are multiple indexes, the largest table corresponding to the index will be the first table scanned

3. For join, left join and right join, whether the large table is in front or the small table is in front, if the where condition does not hit the index, the system will automatically select the small table as the first scan table

About MySql:

1. For a join, whether the large table or the small table comes first, if the where condition hits the index, the small table is always scanned first, not the table where the index is located

2. For left join and right join, the table corresponding to the index will be the first scanned table if the where condition hits the index, whether the large table or the small table is the first

3. For left join and right join, whether the large table or the small table is in the front, if there is no conditional hit index, the main table is the first scanned table

So: the execution order of the table is not transferred by the will of the person in charge. If you don't know, please don't say it casually. Practice is the only criterion for testing truth