SQL必知必会 - 创建表的联结

目录

一、不同类型的联结

1 外联结

 2 内联结

二、用union联结

三、用where创建联结

1 用where简单联结

2 用where and的结合

四、 创建高级联结（用AS设置表别名）

---

inner join：2表值都存在

outer join：附表中值可能存在null的情况。

①A inner join B：取交集

②A left join B：取A全部，B没有对应的值，则为null

③A right join B：取B全部，A没有对应的值，则为null

④A full outer join B：取并集，彼此没有对应的值为null

上述4种的对应条件，在on后填写。

一、不同类型的联结

1 外联结

• 用于联结没有关联的表。LEFT和RIGHT表示要联结的表，位于from表的左边还是右边
• 外联结会设置主表，主表即以该表的数据为主，作为整体的主键。

外联结类型	语句	补充说明
右外联结	RIGHT OUTER JOIN	右边的表 b 是主表，把右边关系中要舍弃的元组保留
左外联结	a LEFT OUTER JOIN b	以左边表a的列为主，取两列的交集， 对于不在右边列存在的取null
全外联结	FULL OUTER JOIN	检索所有行（包含两个表的不关联的行），把两个关系中要舍弃的元组保留

e.g1.  用左外联结，检索出所有顾客的顾客id和订单编号，包括没有订单的顾客

SELECT customers.cust_id,orders.order_num
       FROM customers
LEFT OUTER JOIN orders 
     ON customers.cust_id = orders.id;

 2 内联结

形式：

• 即等值联结，是基于两个表的相等测试。等价于 JOIN 
• inner join只会对非null值作join，并且两边都有才会匹配上 
• 等值语法 INNER JOIN...ON  取两个表的交集，理解图如下：

e.g1. 上述同一个例子，用 INNER JOIN...ON来表述

SELECT vend_name,prod_name,prod_price
       FROM vendors
INNER  JOIN products
       ON vendors.vend_id = products.vend_id;

e.g2. 返回prod_id 为BR01 产品的所有顾客的电子邮件（Customers 表中的 cust_email）最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

select cust_email
from Orders
inner join OrderItems on Orders.order_num=OrderItems.order_num
inner join Customers on Orders.cust_id=Customers.cust_id
where prod_id='BR01'

 【示例结果】返回顾客email cust_email

 e.g3. 【同表数据源的内联结】可以只写join

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

SELECT
    weather.id AS id
FROM
    weather
 JOIN
    weather w 
  ON DATEDIFF(weather.recordDate, w.recordDate) = 1
  AND weather.Temperature > w.Temperature;

二、用union联结

• 用来合并两条sql的结果集
• union--连接表，对行操作。join---连接表，对列操作

• union--将两个表做行拼接，同时自动删除重复的行。
• union all---将两个表做行拼接，保留重复的行。

e.g1. 表OrderItems包含字段prod_id代表产品id、quantity代表产品数量。将两个 SELECT 语句结合。一个 SELECT 语句过滤数量为100的行，另一个过滤 id 以BNBG开头的产品

select  prod_id,quantity
from OrderItems 
where prod_id like 'BNBG%'
union  
select  prod_id,quantity
from OrderItems 
where quantity=100；

e.g2. 从user_profile表中分别查看学校为山东大学或者性别为女性用户的device_id、gender、age和gpa数据，结果不去重

select  device_id, gender, age, gpa
from user_profile
where university='山东大学'

union all

select device_id, gender, age, gpa
from user_profile
where gender='male'；

三、用where创建联结

1 用where简单联结

表现形式

指定要联结的所有表、联结它们的方式

e.g.输出vendors表中的经销商名称，products表中的商品名称和商品价格，其中两个表的关联是 经销商id是对应的

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id;

注意要点

• 多个表被引用时，需要使用完全列名 （用点隔开表名＆列名）
• 直接在from后面加上引用的多个表名
• 作为过滤条件，将多个表中的行对应匹配
• 要保证所有的联结，都有where子句

2 用where and的结合

① 自然联结

• 作用：排除同个列多次返回出现，使每列都只返回一次
• 即只选择那些唯一的列 → 只对一个表使用通配符 *  → 其他表作为子集，用明确的限定列名

e.g. 选择出customers表中购买了RGAN01产品的顾客的所有信息，以及orders表的订单编号和订单日期，orderitems表的item_price，联结关系是c表和o表的顾客id对应一致，oi表和o表的订单编号对应一致

SELECT c.*, o.order_num, o.order_date, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id 
      AND oi.order_num = o.order_num
      AND prod_name = 'RGAN01';

② 联结多个表

• 列出待联结的所有表 → 定义各个表的关系条件 → 用where和and联结各个条件
• 可联结的表一般不限制数目

e.g1. 从orderitems,products,vendors表中，输出订单号为20007的产品名称，经销商名称，产品价格和数量。其中的限定条件是products和vendors表的经销商id一致，orderitems和products表的产品id一致

SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id = vendors.vend_id,
      AND orderitems.prod_id = products.vend_id
      AND order_num ='20007';

e.g2. 从customers表中返回 购买了产品RGAN01的顾客的姓名和联系方式，其中customers表和orders表的关联是顾客id对应，orderitems和orders的订单编号对应

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
       AND orderitems.order_num = orders.order_num
       AND prod_name = 'RGAN01'; 

 e.g.3  给和jim jones同公司的顾客发邮件（先找出Jim Jones的公司 → 找出公司的所有顾客）表为customers，列为顾客id，顾客姓名和顾客的联系方式。

--联结的语句
SELECT c1.cust_id,c1.cust_name,c2.cust_contact
FROM customers AS c1,customers AS c2
WHERE c1.cust_name = c2.cust_name
      AND c2.cust_contact = 'jim jones';

--子查询语句
SELECT cust_id,cust_name,custt_contact
FROM customers
WHERE cust_name = (SELECT cust_name
                   FROM customers
                   WHERE cust_contact = 'jim jones');

四、 创建高级联结（用AS设置表别名）

• 作用：缩短语句；允许一条select语句中多次引用同一张表
• 注：表别名只在查询执行中使用，不会返回到客户端

e.g. 以同样的例子为例 从customers表中返回 购买了产品RGAN01的顾客的姓名和联系方式，其中customers表和orders表的关联是顾客id对应，orderitems和orders的订单编号对应

SELECT cust_name,cust_contact
FROM customers AS C, orders AS O, orderitems AS OI
WHERE C.cust_id = O.cust_id
      AND OI.order_num = O.order_num
      AND prod_name = 'RGAN01';