实验,只是验证一下union和union all的区别,很简单。(Oracle和mysql)
Oracle
z@test10gr2> create table o1 (id number(10),name varchar2(100)); Table created. z@test10gr2> create table o2 (id number(10),name varchar2(100)); Table created. z@test10gr2> create table o3 (id number(10),name varchar2(100)); Table created. z@test10gr2> insert into o1 values(1,'TOM'); 1 row created. z@test10gr2> insert into o2 values(1,'TOM'); 1 row created. z@test10gr2> insert into o1 values(2,'ARUP'); 1 row created. z@test10gr2> insert into o1 values(3,'JACK'); 1 row created. z@test10gr2> commit; Commit complete. z@test10gr2> select * from o1; ID NAME z@test10gr2> select * from o2; ID NAME z@test10gr2> select * from o1 ID NAME z@test10gr2> select * from o1 ID NAME |
mysql
mysql> create table t1 (id int(10),name varchar(100));
Query OK, 0 rows affected (0.06 sec) mysql> create table t2 (id int(10),name varchar(100)); Query OK, 0 rows affected (0.04 sec) mysql> insert into t1 values(1,'TOM'); Query OK, 1 row affected (0.02 sec) mysql> insert into t2 values(1,'TOM'); Query OK, 1 row affected (0.03 sec) mysql> insert into t1 values(2,'ARUP'); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 values(3,'JACK'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM T1 -> ; +------+------+ | id | name | +------+------+ | 1 | TOM | | 2 | ARUP | | 3 | JACK | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM T2 -> ; +------+------+ | id | name | +------+------+ | 1 | TOM | +------+------+ 1 row in set (0.00 sec) mysql> select * from t1 -> union -> select * from t2; +------+------+ | id | name | +------+------+ | 1 | TOM | | 2 | ARUP | | 3 | JACK | +------+------+ 3 rows in set (0.00 sec) mysql> select * from t1 -> union all -> select * from t2; +------+------+ | id | name | +------+------+ | 1 | TOM | | 2 | ARUP | | 3 | JACK | | 1 | TOM | +------+------+ 4 rows in set (0.00 sec) |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23650854/viewspace-687145/,如需转载,请注明出处,否则将追究法律责任。