Detailed explanation of Mysql's method of optimizing sql with join
- 2021-12-11 09:18:58
- OfStack
0. Prepare the relevant tables for the next test
Please see: https://github.com/YangBaohust/my_sql for related table building statements
user1 Table, learning group
+----+-----------+-----------------+---------------------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+---------------------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
| 5 | NULL | Bai Longma | 993267899 |
+----+-----------+-----------------+---------------------------------+
user2 Watch, Wukong's circle of friends
+----+--------------+-----------+
| id | user_name | comment |
+----+--------------+-----------+
| 1 | Monkey King | Monkey King |
| 2 | Niu Mowang | Niu Ge |
| 3 | Princess Iron Fan | Mrs. Niu |
| 4 | Bodhi Old Ancestor | Grapes |
| 5 | NULL | Crystal crystal |
+----+--------------+-----------+
user1_kills Table, the number of monsters killed on the way to learn from the scriptures
+----+-----------+---------------------+-------+
| id | user_name | timestr | kills |
+----+-----------+---------------------+-------+
| 1 | Monkey King | 2013-01-10 00:00:00 | 10 |
| 2 | Monkey King | 2013-02-01 00:00:00 | 2 |
| 3 | Monkey King | 2013-02-05 00:00:00 | 12 |
| 4 | Monkey King | 2013-02-12 00:00:00 | 22 |
| 5 | Pig 8 Precept | 2013-01-11 00:00:00 | 20 |
| 6 | Pig 8 Precept | 2013-02-07 00:00:00 | 17 |
| 7 | Pig 8 Precept | 2013-02-08 00:00:00 | 35 |
| 8 | Sand monk | 2013-01-10 00:00:00 | 3 |
| 9 | Sand monk | 2013-01-22 00:00:00 | 9 |
| 10 | Sand monk | 2013-02-11 00:00:00 | 5 |
+----+-----------+---------------------+-------+
user1_equipment Table, learning group equipment
+----+-----------+--------------+-----------------+-----------------+
| id | user_name | arms | clothing | shoe |
+----+-----------+--------------+-----------------+-----------------+
| 1 | Tang's monk | 9 Ring tin scepter | Brocade cassock | Monk shoes |
| 2 | Monkey King | Gold-banded cudgel | Shuttle Golden Armor | Lotus root silk step cloud shoe |
| 3 | Pig 8 Precept | 9 Toothed rake | A monk's garment | Monk shoes |
| 4 | Sand monk | Demon Reducing Treasure Staff | A monk's garment | Monk shoes |
+----+-----------+--------------+-----------------+-----------------+
1. Optimize the not in clause using left join
Example: Find out the people in the Buddhist scriptures group who do not belong to Wukong's circle of friends
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
not in writing:
select * from user1 a where a.user_name not in (select user_name from user2 where user_name is not null);
left join writing:
First, look at the external connection data set connected through user_name
select a.*, 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 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
It can be seen that all the data in the a table are displayed, and the data in the b table are displayed only when b.user_name and a.user_name are equal, and the rest are filled with null values. To find out those who do not belong to Wukong's circle of friends in the learning group, only one filter condition b.user_is null needs to be added to 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's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
| 5 | NULL | Bai Longma | 993267899 |
+----+-----------+-----------------+-----------------------+
Seeing that there is one more Bai Longma in the result set, just 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;
2. Optimize scalar queries using left join
Example: Check the nicknames of people in the Buddhist scriptures group in Wukong's circle of friends
+-----------+-----------------+-----------+
| user_name | comment | comment2 |
+-----------+-----------------+-----------+
| Tang's monk | Zha Tan Gong De Buddha | NULL |
| Monkey King | Fight over Buddha | Monkey King |
| Pig 8 Precept | Clean altar messenger | NULL |
| Sand monk | Arhat in Golden Body | NULL |
| NULL | Bai Longma | NULL |
+-----------+-----------------+-----------+
Subquery writing:
select a.user_name, a.comment, (select comment from user2 b where b.user_name = a.user_name) comment2 from user1 a;
left join writing:
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
3. Optimize aggregate subqueries using join
Example: Find out the date when each person in the learning group plays the most monsters
+----+-----------+---------------------+-------+
| id | user_name | timestr | kills |
+----+-----------+---------------------+-------+
| 4 | Monkey King | 2013-02-12 00:00:00 | 22 |
| 7 | Pig 8 Precept | 2013-02-08 00:00:00 | 35 |
| 9 | Sand monk | 2013-01-22 00:00:00 | 9 |
+----+-----------+---------------------+-------+
Aggregation 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 writing:
First of all, look at the self-related result set of the two tables. In order to save space, only take the strange data of Pig 8 Rings
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
It can be seen that when two tables are self-correlated through user_name, only one group by needs to be performed on all fields of a table, and max (kills) in b table is taken, so long as a. kills=max (b. kills) meets the requirements. sql is as follows
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
4. Packet selection using join
Example: Upgrade the third example, and find out the first two dates when each person in the learning group plays the most monsters
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
In oracle, it can be realized by analyzing functions
select b.* from (select a.*, row_number() over(partition by user_name order by kills desc) cnt from user1_kills a) b where b.cnt <= 2;
Unfortunately, the above sql reported an error of ERROR 1064 (42000) in mysql: You have an error in your SQL syntax; Because mysql does not support parsing functions. But it can be achieved in the following ways.
Firstly, the two tables are self-related. In order to save space, only the data of the Monkey King is taken out
select a.*, b.* from user1_kills a join user1_kills b on (a.user_name=b.user_name and a.kills<=b.kills) order by a.user_name, a.kills desc;
+----+-----------+-----------------+-----------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------+-----------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
+----+-----------+-----------------+-----------------------+
From the above table, we know that the top two numbers of Monkey King are 22 and 12, so we only need to make an group by for all fields of a table, make an count for id of b table, and the count value is less than or equal to 2 to meet the requirements. sql is rewritten as follows:
select a.* from user1_kills a join user1_kills b on (a.user_name=b.user_name and a.kills<=b.kills) group by a.id, a.user_name, a.timestr, a.kills having count(b.id) <= 2;
5. Using Cartesian product association to realize one column to multiple rows
Example: Change each phone number in the learning group into one line
Original data:
+-----------+---------------------------------+
| user_name | mobile |
+-----------+---------------------------------+
| Tang's monk | 138245623,021-382349 |
| Monkey King | 159384292,022-483432,+86-392432 |
| Pig 8 Precept | 183208243,055-8234234 |
| Sand monk | 293842295,098-2383429 |
| NULL | 993267899 |
+-----------+---------------------------------+Data you want:
+-----------+-------------+
| user_name | mobile |
+-----------+-------------+
| Tang's monk | 138245623 |
| Tang's monk | 021-382349 |
| Monkey King | 159384292 |
| Monkey King | 022-483432 |
| Monkey King | +86-392432 |
| Pig 8 Precept | 183208243 |
| Pig 8 Precept | 055-8234234 |
| Sand monk | 293842295 |
| Sand monk | 098-2383429 |
| NULL | 993267899 |
+-----------+-------------+
You can see that Tang's monk has two telephones, so he needs two lines. We can calculate the number of telephone numbers of each person first, and then carry out Cartesian product association with a sequence table. In order to save space, only the data of Tang's monk is taken out
select a.id, b.* from tb_sequence a cross join (select user_name, mobile, length(mobile)-length(replace(mobile, ',', ''))+1 size from user1) b order by 2,1;
+----+-----------+---------------------------------+------+
| id | user_name | mobile | size |
+----+-----------+---------------------------------+------+
| 1 | Tang's monk | 138245623,021-382349 | 2 |
| 2 | Tang's monk | 138245623,021-382349 | 2 |
| 3 | Tang's monk | 138245623,021-382349 | 2 |
| 4 | Tang's monk | 138245623,021-382349 | 2 |
| 5 | Tang's monk | 138245623,021-382349 | 2 |
| 6 | Tang's monk | 138245623,021-382349 | 2 |
| 7 | Tang's monk | 138245623,021-382349 | 2 |
| 8 | Tang's monk | 138245623,021-382349 | 2 |
| 9 | Tang's monk | 138245623,021-382349 | 2 |
| 10 | Tang's monk | 138245623,021-382349 | 2 |
+----+-----------+---------------------------------+------+
a. id corresponds to the telephone number, and size is the total number of telephone numbers, so you can add the association condition (a. id)
<
= b. size), continue to adjust the above sql
select b.user_name, replace(substring(substring_index(b.mobile, ',', a.id), char_length(substring_index(mobile, ',', a.id-1)) + 1), ',', '') as mobile from tb_sequence a cross join (select user_name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',', ''))+1 size from user1) b on (a.id <= b.size);
6. Using Cartesian product association to realize multi-column to multi-row
Example: Change each piece of equipment in the learning group into one line
Original data:
+----+-----------+--------------+-----------------+-----------------+
| id | user_name | arms | clothing | shoe |
+----+-----------+--------------+-----------------+-----------------+
| 1 | Tang's monk | 9 Ring tin scepter | Brocade cassock | Monk shoes |
| 2 | Monkey King | Gold-banded cudgel | Shuttle Golden Armor | Lotus root silk step cloud shoe |
| 3 | Pig 8 Precept | 9 Toothed rake | A monk's garment | Monk shoes |
| 4 | Sand monk | Demon Reducing Treasure Staff | A monk's garment | Monk shoes |
+----+-----------+--------------+-----------------+-----------------+Data you want:
+-----------+-----------+-----------------+
| user_name | equipment | equip_mame |
+-----------+-----------+-----------------+
| Tang's monk | arms | 9 Ring tin scepter |
| Tang's monk | clothing | Brocade cassock |
| Tang's monk | shoe | Monk shoes |
| Monkey King | arms | Gold-banded cudgel |
| Monkey King | clothing | Shuttle Golden Armor |
| Monkey King | shoe | Lotus root silk step cloud shoe |
| Sand monk | arms | Demon Reducing Treasure Staff |
| Sand monk | clothing | A monk's garment |
| Sand monk | shoe | Monk shoes |
| Pig 8 Precept | arms | 9 Toothed rake |
| Pig 8 Precept | clothing | A monk's garment |
| Pig 8 Precept | shoe | Monk shoes |
+-----------+-----------+-----------------+
union is written in:
select user_name, 'arms' as equipment, arms equip_mame from user1_equipment
union all
select user_name, 'clothing' as equipment, clothing equip_mame from user1_equipment
union all
select user_name, 'shoe' as equipment, shoe equip_mame from user1_equipment
order by 1, 2;
join is written:
First of all, look at the effect of Descartes data set, taking Tang Seng as an example
select a.*, b.* from user1_equipment a cross join tb_sequence b where b.id <= 3;
+----+-----------+--------------+-----------------+-----------------+----+
| id | user_name | arms | clothing | shoe | id |
+----+-----------+--------------+-----------------+-----------------+----+
| 1 | Tang's monk | 9 Ring tin scepter | Brocade cassock | Monk shoes | 1 |
| 1 | Tang's monk | 9 Ring tin scepter | Brocade cassock | Monk shoes | 2 |
| 1 | Tang's monk | 9 Ring tin scepter | Brocade cassock | Monk shoes | 3 |
+----+-----------+--------------+-----------------+-----------------+----+
Use case to process the above results
select user_name,
case when b.id = 1 then 'arms'
when b.id = 2 then 'clothing'
when b.id = 3 then 'shoe' end as equipment,
case when b.id = 1 then arms end arms,
case when b.id = 2 then clothing end clothing,
case when b.id = 3 then shoe end shoe
from user1_equipment a cross join tb_sequence b where b.id <=3;
+-----------+-----------+--------------+-----------------+-----------------+
| user_name | equipment | arms | clothing | shoe |
+-----------+-----------+--------------+-----------------+-----------------+
| Tang's monk | arms | 9 Ring tin scepter | NULL | NULL |
| Tang's monk | clothing | NULL | Brocade cassock | NULL |
| Tang's monk | shoe | NULL | NULL | Monk shoes |
+-----------+-----------+--------------+-----------------+-----------------+
Merge multiple columns of data using the coalesce function
select user_name,
case when b.id = 1 then 'arms'
when b.id = 2 then 'clothing'
when b.id = 3 then 'shoe' end as equipment,
coalesce(case when b.id = 1 then arms end,
case when b.id = 2 then clothing end,
case when b.id = 3 then shoe end) equip_mame
from user1_equipment a cross join tb_sequence b where b.id <=3 order by 1, 2;
7. Update the table that contains itself in the filter criteria using join
Example: Update the comment field to "This person is in Wukong's circle of friends" for a person who exists in both the Buddhist scriptures group and Wukong's circle of friends.
It would be natural for us to first find out who has user_name in both user1 and user2, and then update the user1 table, with sql as follows
update user1 set comment = ' This person is in Wukong's circle of friends ' where user_name in (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name));Unfortunately, the above sql reported an error in mysql: ERROR 1093 (HY000): You can 't specify target table' user1 'for update in FROM clause, indicating that the target table cannot be updated in the from clause.
Is there any other way? We can convert the writing of in into the writing of join
select c.*, d.* from user1 c join (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name)) d on (c.user_name = d.user_name);
+----+-----------+--------------+---------------------------------+-----------+
| id | user_name | comment | mobile | user_name |
+----+-----------+--------------+---------------------------------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | Monkey King |
+----+-----------+--------------+---------------------------------+-----------+
Then update the view after join
update user1 c join (select a.user_name from user1 a join user2 b on (a.user_name = b.user_name)) d on (c.user_name = d.user_name) set c.comment = ' This person is in Wukong's circle of friends ';
If you look at user1 again, you can see that user1 has been modified successfully
select * from user1;
+----+-----------+-----------------------------+---------------------------------+
| id | user_name | comment | mobile |
+----+-----------+-----------------------------+---------------------------------+
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 |
| 2 | Monkey King | This person is in Wukong's circle of friends | 159384292,022-483432,+86-392432 |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 |
| 5 | NULL | Bai Longma | 993267899 |
+----+-----------+-----------------------------+---------------------------------+
8. Delete duplicate data using join
First, insert two pieces of data into the user2 table
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+Example: Delete the duplicate data in the user2 table, and keep only the large id number
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
Look at duplicate records first
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
Then just delete (a. id
<
b. id)
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
Looking at user2, you can see that the duplicate data has been deleted
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| id | user_name | comment | mobile | id | user_name | comment |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+
| 2 | Monkey King | Fight over Buddha | 159384292,022-483432,+86-392432 | 1 | Monkey King | Monkey King |
| 1 | Tang's monk | Zha Tan Gong De Buddha | 138245623,021-382349 | NULL | NULL | NULL |
| 3 | Pig 8 Precept | Clean altar messenger | 183208243,055-8234234 | NULL | NULL | NULL |
| 4 | Sand monk | Arhat in Golden Body | 293842295,098-2383429 | NULL | NULL | NULL |
| 5 | NULL | Bai Longma | 993267899 | NULL | NULL | NULL |
+----+-----------+-----------------+---------------------------------+------+-----------+-----------+Summary:
To introduce to you here, we are interested in creating more data, and then compare the difference between different sql writing in execution time. The example in this paper is taken from "sql Development Skills" of massive open online course.