实验，只是验证一下union和union all的区别，很简单。（Oracle和mysql）
- UNION 将两个SQL语句的结果合并起来，并去重。
- union all 会显示全部记录。
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/，如需转载，请注明出处，否则将追究法律责任。
