SQL基础教程——联结（第七章第二节）

1、内联结——INNER JOIN

集合运算会导致行数的增减。联结运算就是讲其他表中的列添加过来，进行“添加列”的运算。

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. 因为联结会用到多张表，推荐使用别名
2. 我们可以在ON之后指定两张表联结所使用的列（联结键）
3. 在SELECT子句中使用“<表的别名>.<列名>”的形式来指定列

2、外联结——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;

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 sp.product_id=ip.product_id;

4、交叉联结——CROSS JOIN（笛卡尔积）

SELECT SP.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
FROM shopproduct AS sp CROSS JOIN product AS p
ON sp.product_id=p.product_id;