一句话总结:union all是求所有合集,不去重;union要去重,效率要比union all 低一些。
mysql> SELECT * FROM table1;
+----+------+---------------------+
| id | name | mobile |
+----+------+---------------------+
| 1 | jack | 3333,4444,5555 |
| 2 | john | 1111,2222 |
| 3 | amry | 6666,7777,8888,9999 |
+----+------+---------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM table1 union all SELECT * FROM table1;
+----+------+---------------------+
| id | name | mobile |
+----+------+---------------------+
| 1 | jack | 3333,4444,5555 |
| 2 | john | 1111,2222 |
| 3 | amry | 6666,7777,8888,9999 |
| 1 | jack | 3333,4444,5555 |
| 2 | john | 1111,2222 |
| 3 | amry | 6666,7777,8888,9999 |
+----+------+---------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM table1 union SELECT * FROM table1;
+----+------+---------------------+
| id | name | mobile |
+----+------+---------------------+
| 1 | jack | 3333,4444,5555 |
| 2 | john | 1111,2222 |
| 3 | amry | 6666,7777,8888,9999 |
+----+------+---------------------+
3 rows in set (0.00 sec)
mysql>