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

How to optimize sql with join in Mysql? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

0. Prepare the relevant table for the next test

User1 table Learn from the classics group +-+-+ | id | user_name | comment | mobile | +-+-- -+-+ | 1 | Tang Monk | Chandraguptan Gongde Buddha | 138245623021-382349 | | 2 | Sun WuKong | fighting over Buddha | 159384292022-483432 + 86-392432 | | 3 | Zhu Bajie | Jingtan Messenger | 183208243055-8234234 | | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 | | 5 | NULL | Bailongma | 993267899 | +-+ user2 table Wukong's moments +-+ | id | user_name | comment | +-- + | 1 | Sun WuKong | Monkey King | | 2 | Bull Devil King | Brother Niu | | 3 | Princess Tiefan | Madame Niu | 4 | Bodhi Laozu | | Grape | | 5 | NULL | Jingjing | +-+ user1_ Kills table | Number of monsters killed on the road +-+ | id | user_name | timestr | kills | +-+ | 1 | Sun Wu Empty | 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_ equation table Learning from classics group equipment +-+ | id | user_name | arms | clothing | shoe | +-+ -+-+ | 1 | Tang monk | Jiuhuan tin staff | Jinlai cassock | monk shoes | 2 | Sun WuKong | Golden Hoop stick | Shuttle Golden armour | Lotus root step cloud shoes | 3 | Zhu Bajie | Nine-toothed nail rake | monk's clothes | monk's shoes | 4 | Sha Wujing | Devil's scepter | monk's clothes | monk's shoes Shoes | +-+

1. 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 is written:

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 | defeat Buddha in battle | 159384292022-483432 | + 86-392432 | 1 | Sun WuKong | Monkey King | 1 | Tang monk | Chandragupta merit Defoe | 138245623021-382349 | NULL | | 3 | Zhu Bajie | Jingtan Messenger | 183208243055-82342234 | NULL | | 4 | Sha Wujing | Golden body Luohan | 293842295098-2383429 | NULL | 5 | NULL | Bailongma | 993267899 | 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

two。 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 | Chandler Tan Gong de Buddha | NULL | | Sun WuKong | fight over Buddha | 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 | 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 | Pig | 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)

4. 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.