sql 6 集合运算

sql 6 集合运算

集合运算

集合运算表示对满足同一规则的记录进行四则运算，通过集合运算可以得到两张表中记录的集合或者公共记录的集合，或者其中某张表中的集合。

union（并集）

union让表相加，可以去掉重复的记录

语法格式：select语句 union select语句;

product表和product2表进行加法运算（求并集）

select product_id,product_name from product
union
select product_id,product_name from product2;

集合运算的注意事项：

1）作为运算对象的记录的列数必须相同
2）作为运算对象的记录的列的类型必须一致
3）可以使用任何select语句，但order by子句只能在最后使用一次

select product_id,product_name from product where product_type= '厨房用具'
union
select product_id,product_name from product2 where product_type= '厨房用具' order by product_id;

all选项 保留重复的记录

product表和product2表重复的记录会保留下来

select product_id,product_name from product
union all
select product_id,product_name from product2;

intersect（交集）

intersect取表中相同的记录，MySQL不支持

语法格式：select语句 intersect select语句;

product表和product2表中的相同记录（求交集）

select product_id,product_name from product
intersect
select product_id,product_name from product2;

except（差集）

except让表进行减法运算，Oracle改为minus，MySQL不支持

语法格式：select语句 except select语句;

在product表中去掉product2表所有记录

select product_id,product_name from product
expect
select product_id,product_name from product2;

在product2表中去掉product表所有记录

select product_id,product_name from product2
expect
select product_id,product_name from product;

这个sql语句的结果并不一样

join（联结）

上面讲的union intersect except都是属于以行方向为单位进行操作。简单的说就这些集合运算是会导致行数的增减。联结是将其他表中的列添加过来的运算，因此联结是属于以列方向为单位进行操作的运算。它可分为内联结和外联结

inner join（内联结）

语法格式：select 子句 from 表1 inner join 表2 on 联结键;

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id;

注意事项：

1）from子句中有两张表 别名可有可无

from shopproduct as sp inner join product as p

2）on子句写在from和where之间，on子句指定两张表联结所使用的的列（联结键）

on sp.product_id = p.product_id

3）select子句按照<表名/表的别名>.<列名>格式书写

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price

联结结合where

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id
where sp.shop_id = '000A';

因此，同样也联结也可以group by，having，order by结合一起使用。

outer join（外联结）

外联结和内联结一样，都是把两张表通过联结键联结在一起，只是结果不一样

语法格式：
select 子句 from 表1 left outer join 表2 on 联结键;
select 子句 from 表1 right outer join 表2 on 联结键;

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct as sp right outer join product as p
on sp.product_id = p.product_id;

注意事项：

1）可以得到单张表的全部信息。上面的结果是得到product表的全部信息
2）通过left right来指定from子句中左边还是右边的表为主表，结果会包含主表中的所有信息

3张以上的表的联结

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id
inner join inventoryproduct as ip 
on p.product_id = ip.product_id;

cross join（交叉联结/笛卡儿积）

语法格式：select 子句 from 表1 cross join 表2 ;

select sp.shop_id, sp.shop_name, sp.product_id, p.product_name 
from shopproduct as sp cross join product as p;

结果为shopproduct表的行数 * product表的行数。由于结果行数多和没有实际价值，一般很少使用交叉联结