SQL高级查询--集合操作符

集合操作符可以将两个或多个查询返回的行组合起来，以下表给出了4种集合操作符


                                            集合操作符
--------------------------------------------------------------------------------------------------------------------------------------------
操作符                |                                                                 说明
---------------------------------------------------------------------------------------------------------------------------------------------
union   all           |   返回各个查询检索出的所有行，包括重复的行
--------------------------------------------------------------------------------------------------------------------------------------------
union                 |    返回各个查询检索出的所有行，不包括重复的行
-------------------------------------------------------------------------------------------------------------------------------------------
intersect            |   返回两个查询检索出的共有行
---------------------------------------------------------------------------------------------------------------------------------------------
minus              |     返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行
-------------------------------------------------------------------------------------------------------------------------------------------


当使用集合操作符的时候，必须牢记这样一个限制条件，所有查询返回的列数以及列的类型必须匹配，但列名可以不同。


接下来用示例展示各集合操作符的功能

1. 示例表

products和more_products表创建语句如下：
create table products (
 product_id integer
   constraint products_pk  PRIMARY KEY,
 product_type_id integer
    constraint products_fk_product_types
    references product_types(product_type_id),
name varchar2(30) not null,
description varchar2(50),
price number(5,2)
);
create table more_products (
 prd_id integer
   constraint more_products_pk  PRIMARY KEY,
 prd_type_id integer
    constraint more_products_fk_product_types
    references product_types(product_type_id),
name varchar2(30) not null,
available char(1)
);
下面这个查询从products表中检索product_id,product_type_id和name列
select  product_id,product_type_id,name
from products;
product_id  product_type_id     name
--------------  ----------------------    ------------------------------------------------------------
                1                             1    Modern Science
                2                            1    Chemistry
                3                             2     Supernova
                4                             2   Tank War
               5                              2    Z Files
               6                               2   2412: The Return
               7                              3    Space  Force 9
              8                               3     From Another Planet
               9                              4     Classical Music
              10                             4   Pop 3
               11                           4     Creative  Yell
              12                                     My Front Line


下面这个查询从more_products表中检索prd_id,prd_type_id和name列
select prd_id,prd_type_id,name
from  more _products;
prd_id        prd_type_id          name
----------    -------------------        ---------------------------------------------------------
          1                            1         Modern Science
          2                            1         Chemistry
          3                                        Supernova
          4                             2        Lunar Landing
          5                             2        Submarine

 

2,  使用union all操作符

union all操作符返回查询检索出的所有行，包括重复的行，下面这个查询使用了union all ，注意products和more_products中的所有行都被检索出来了，包括重复的行。
select  product_id,product_type_id,name
from products
union all
select prd_id,prd_type_id,name
from  more _products;


product_id  product_type_id     name
--------------  ----------------------    ------------------------------------------------------------
                1                             1    Modern Science
                2                            1    Chemistry
                3                             2     Supernova
                4                             2   Tank War
               5                              2    Z Files
               6                               2   2412: The Return
               7                              3    Space  Force 9
              8                               3     From Another Planet
               9                              4     Classical Music
              10                             4   Pop 3
               11                           4     Creative  Yell
              12                                     My Front Line
          1                            1         Modern Science
          2                            1         Chemistry
          3                                        Supernova
          4                             2        Lunar Landing
          5                             2        Submarine


可以使用order by 字句对上面进行排序

 

3. 使用union操作符

union操作符返回查询检索出的所有非重复行。下面例子使用了union，注意重复的Modern Science和Chemistry行未被检索，因此只返回15行
select  product_id,product_type_id,name
from products
union 
select prd_id,prd_type_id,name
from  more _products;
product_id  product_type_id     name
--------------  ----------------------    ------------------------------------------------------------
                1                             1    Modern Science
                2                            1    Chemistry
                3                             2     Supernova          
               3                                        Supernova
                4                             2   Tank War
               4                             2        Lunar Landing
               5                              2    Z Files
              5                             2        Submarine
               6                               2   2412: The Return
               7                              3    Space  Force 9
              8                               3     From Another Planet
               9                              4     Classical Music
              10                             4   Pop 3
               11                           4     Creative  Yell
              12                                     My Front Line

4. 使用intersect操作符

intersect操作符返回两个查询检索出的共有行，下面例子使用了intersect，注意返回了Modern Science和Chemistry行
select  product_id,product_type_id,name
from products
intersect
select prd_id,prd_type_id,name
from  more _products;


product_id  product_type_id     name
--------------  ----------------------    ------------------------------------------------------------
                1                             1    Modern Science
                2                            1    Chemistry

5. 使用minus操作符


minus操作符返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行，下面例子使用minus。
注意先从products中返回的行中减去从more_products中返回的行，然后再返回剩余的行
select  product_id,product_type_id,name
from products
minus
select prd_id,prd_type_id,name
from  more _products;


product_id  product_type_id     name
--------------  ----------------------    ------------------------------------------------------------
                3                             2     Supernova          
                4                             2   Tank War
               5                              2    Z Files
               6                               2   2412: The Return
               7                              3    Space  Force 9
              8                               3     From Another Planet
               9                              4     Classical Music
              10                             4   Pop 3
               11                           4     Creative  Yell
              12                                     My Front Line

 

<待续……