ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql中union和union all

sql中union和union all

原创 Linux操作系统 作者:zecaro 时间:2011-02-14 13:15:21 0 删除 编辑

          实验,只是验证一下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
---------- ------------------------------
         1 TOM
         2 ARUP
         3 JACK

z@test10gr2> select * from o2;

        ID NAME
---------- ------------------------------
         1 TOM

z@test10gr2> select * from o1
  2  union
  3  select * from o2;

        ID NAME
---------- ------------------------------
         1 TOM
         2 ARUP
         3 JACK

z@test10gr2> select * from o1
  2  union all
  3  select * from o2;

        ID NAME
---------- ------------------------------
         1 TOM
         2 ARUP
         3 JACK
         1 TOM

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-10-28

  • 博文量
    182
  • 访问量
    348634