mysql：union与union all的区别

来源：互联网发布：ubuntu ntp server 编辑：程序博客网时间：2024/05/20 05:09

一句话总结：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>

阅读全文

0 0