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

This article mainly explains "how mysql uses Join to optimize SQL sentences". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how mysql uses Join to optimize SQL sentences".

Prepare the relevant table

For related table sentences, please see: https://github.com/YangBaohust/my_sql

User1 table, meridian group

+-+-

| | id | user_name | comment | mobile | |

+-+-

| | 1 | Tang Monk | Chandraguptan Gongde Buddha | 138245623021-382349 |

| | 2 | Sun WuKong | fight to defeat Buddha | 159384292022-483432 moment 86-392432 |

| | 3 | Zhu Bajie | Jingtan messenger | 183208243055-8234234 |

| | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 |

| | 5 | NULL | Bailongma | 993267899 | |

+-+-

User2 watch, Wukong's moments

+-- +

| | id | user_name | comment | |

+-- +

| | 1 | Sun WuKong | Monkey King |

| | 2 | Bull Devil King | Brother Niu |

| | 3 | Princess Iron Fan | Madame Niu |

| | 4 | Bodhi ancestor | Grape |

| | 5 | NULL | Jingjing |

+-- +

User1_kills table, the number of monsters killed on the road.

+-- +

| | id | user_name | timestr | kills | |

+-- +

| | 1 | Sun WuKong | 2013-01-10 00:00:00 | 10 |

| | 2 | Sun WuKong | 2013-02-01 00:00:00 | 2 |

| | 3 | Sun WuKong | 2013-02-05 00:00:00 | 12 |

| | 4 | Sun WuKong | 2013-02-12 00:00:00 | 22 |

| | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 |

| | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

| | 8 | Sha Wujing | 2013-01-10 00:00:00 | 3 |

| | 9 | Sha Wujing | 2013-01-22 00:00:00 | 9 |

| | 10 | Sha Wujing | 2013-02-11 00:00:00 | 5 |

+-- +

User1_equipment table, learning from classics group equipment

+-- +

| | id | user_name | arms | clothing | shoe | |

+-- +

| | 1 | Tang monk | Jiuhuan tin stick | beautiful cassock | monk shoes |

| | 2 | Sun WuKong | Golden Hoop stick | Shuttle Golden Armor | Lotus Root step Cloud shoes |

| | 3 | Zhu Bajie | Nine-toothed nail rake | monk's clothes | monk's shoes |

| | 4 | Sha Wujing | Devil's staff | monk's clothes | monk's shoes |

+-- +

Optimize the not in clause using left join

Example: find out the people in the scripture group who do not belong to Wukong's moments.

+-+-

| | id | user_name | comment | mobile | |

+-+-

| | 1 | Tang Monk | Chandraguptan Gongde Buddha | 138245623021-382349 |

| | 3 | Zhu Bajie | Jingtan messenger | 183208243055-8234234 |

| | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 |

+-+-

Not in is written:

Select * from user1 a where a.user_name not in (select user_name from user2 where user_name is not null)

Left join: first look at the externally connected dataset connected through user_name

Select A. on, b. * from user1 a left join user2 b on (a.user_name = b.user_name)

+-+-

| | id | user_name | comment | mobile | id | user_name | comment | |

+-+-

| | 2 | Sun WuKong | fight to defeat Buddha | 159384292022-483432 moment 86-392432 | 1 | Sun WuKong | Monkey King |

| | 1 | Tang Monk | Chandraguptan Gongdefo | 138245623021-382349 | NULL | NULL | NULL |

| | 3 | Zhu Bajie | Jingtan Messenger | 183208243055-8234234 | NULL | NULL | NULL |

| | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 | NULL | NULL | NULL |

| | 5 | NULL | Bailongma | 993267899 | NULL | NULL | NULL | |

+-+-

You can see that all the data in table an is displayed, and the data in table b is displayed only when b.user_name is equal to a.user_name, and the rest are filled with null values. To find people in the classics group who do not belong to Wukong moments, you only need to add a filter condition b.user_name is null in b.user_name.

Select a.* from user1 a left join user2 b on (a.user_name = b.user_name) where b.user_name is null

+-+-

| | id | user_name | comment | mobile | |

+-+-

| | 1 | Tang Monk | Chandraguptan Gongde Buddha | 138245623021-382349 |

| | 3 | Zhu Bajie | Jingtan messenger | 183208243055-8234234 |

| | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 |

| | 5 | NULL | Bailongma | 993267899 | |

+-+-

See here and find that there is another white dragon horse in the result set. Just continue to add the filter condition a.user_name is not null.

Select a.* from user1 a left join user2 b on (a.user_name = b.user_name) where b.user_name is null and a.user_name is not null

Using left join to optimize scalar quantum query

Example: check the nicknames of the people in the scripture learning group on Wukong moments

+-+

| | user_name | comment | comment2 | |

+-+

| | Tang Monk | Chandraguptan Gongdefo | NULL |

| | Sun WuKong | defeats Buddha in battle | Monkey King |

| | Zhu Bajie | Jingtan Messenger | NULL | |

| | Sha Wujing | Golden body Luohan | NULL | |

| | NULL | Bailongma | NULL | |

+-+

The subquery is written:

Select a.user_name, a.comment, (select comment from user2 b where b.user_name = a.user_name) comment2 from user1 a

Left join is written:

Select a.user_name, a.comment, b.comment comment2 from user1 a left join user2 b on (a.user_name = b.user_name)

3. Optimize aggregation subqueries using join

Example: find out the date on which each person in the scripture-learning group beat the most strangers.

+-- +

| | id | user_name | timestr | kills | |

+-- +

| | 4 | Sun WuKong | 2013-02-12 00:00:00 | 22 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

| | 9 | Sha Wujing | 2013-01-22 00:00:00 | 9 |

+-- +

Aggregate subquery writing:

Select * from user1_kills a where a.kills = (select max (b.kills) from user1_kills b where b.user_name = a.user_name)

Join is written:

First of all, let's take a look at the self-related result sets of the two tables. In order to save space, we only take Zhu Bajie's anti-monster data to see.

Select A. on, b. * from user1_kills a join user1_kills b on (a.user_name = b.user_name) order by 1

+-+-

| | id | user_name | timestr | kills | id | user_name | timestr | kills |

+-+-

| | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 |

| | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 |

| | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

| | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

| | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 |

| | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 | 6 | Zhu Bajie | 2013-02-07 00:00:00 | 17 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

+-+-

You can see that when the two tables are self-associated through user_name, you only need to group by all the fields of table a, take the max (kills) in table b, and as long as a.kills=max (b.kills) meets the requirements. Sql is as follows

Select a.* from user1_kills a join user1_kills b on (a.user_name = b.user_name) group by a.id, a.user_name, a.timestr, a.kills having a.kills = max (b.kills)

Use join for grouping selection

Example: upgrade the third example to find out the first two dates in which each person in the scripture group beat the most strangers.

+-- +

| | id | user_name | timestr | kills | |

+-- +

| | 3 | Sun WuKong | 2013-02-05 00:00:00 | 12 |

| | 4 | Sun WuKong | 2013-02-12 00:00:00 | 22 |

| | 5 | Zhu Bajie | 2013-01-11 00:00:00 | 20 |

| | 7 | Zhu Bajie | 2013-02-08 00:00:00 | 35 |

| | 9 | Sha Wujing | 2013-01-22 00:00:00 | 9 |

| | 10 | Sha Wujing | 2013-02-11 00:00:00 | 5 |

+-- +

In oracle, you can do this by analyzing functions

Select b.* from (select A. over, row_number () over (partition by user_name order by kills desc) cnt from user1_kills a) b where b.cnt

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.