實驗，只是驗證一下union和union all的區別，很簡單。（Oracle和mysql）

UNION 將兩個SQL語句的結果合併起來，並去重。
union all 會顯示全部記錄。

Oracle

> create table o1 (id number(10),name varchar2(100));

Table created.

> create table o2 (id number(10),name varchar2(100));

Table created.

> create table o3 (id number(10),name varchar2(100));

Table created.

> insert into o1 values(1,'TOM');

1 row created.

> insert into o2 values(1,'TOM');

1 row created.

> insert into o1 values(2,'ARUP');

1 row created.

> insert into o1 values(3,'JACK');

1 row created.

> commit;

Commit complete.

> select * from o1;

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

> select * from o2;

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

> select * from o1
2 union
3 select * from o2;

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

> 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)

sql中union和union all

相關文章