联结就是将其他表中的列添加过来,进行“添加列”的集合运算。
union是以行(纵向)为单位进行操作,而联结则是以列(横向)为单位进行的。
内联结:inner join
所谓联结,就是以A表中的列作为桥梁,将B中满足同样条件的列汇集到同一结果中。
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;
product_id 作为公共列联结两表:
内联结要点:1.from子句:进行联结的from子句中使用多张表
2.on子句:在on之后指定两张表联结所使用的列(联结键),进行内联结必须使用on子句,并且要书写在from和where之间。
内联结和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';
联结完成后用where子句添加约束条件:
外联结:outer join
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.选取出单张表中全部的信息,包括null值,想要生成固定行数的单据时,需要使用外联结。
2.外联结又分为左外联结和右外联结。使用left时from子句中写在左侧的表是主表,使用right时右侧的表是主表。
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from Product as p left outer join ShopProduct as sp
on sp.product_id = p.product_id;
结果是完全一样的。通常使用left的情况多一些。
3张表以上的联结
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price, inp.inventory_quantity
from ShopProduct as sp inner join Product as p
on sp.product_id = p.product_id
inner join InventoryProduct as inp
on sp.product_id = inp.product_id
where inp.inventory_id = 'S001';
交叉联结:cross join
交叉联结是所有联结的基础,也是联结的原理所在
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name
from ShopProduct as sp cross join Product as p;
对满足相同规则的表进行交叉联结的集合运算符是cross join(笛卡儿积)。