SQL基础8—表的联结

关键字：
- 等值联结：WHERE
- 非等值联结：WHERE
- 交叉联结cross join/笛卡尔积：无联结条件
- 内联接：[INNER] JOIN
- 外联结：LEFT/RIGHT/FULL JOIN
- 自联结：联结自身
- 多表联结：WHERE子句

---

引言

1.为什么要把数据分多个表进行存储？

• 为了避免数据冗余，更加高效地进行数据处理，可伸缩性能好。
• 可伸缩性：能够适应不断增加的工作量而不失败。

2.创建联结表的原因？

• 联结是一种机制，用来在一条select语句中关联表，用一条语句检索出存储在多个表中的数据。

1.创建联结表

注意：完全限定列名

• 引用的列可能会出现歧义（同名不同表），此时必须使用完全限定列名，使用表名.列名。

1.1 等值联结与笛卡尔积:Where子句

Where子句的作用：

• 作为过滤条件，只包含那些匹配给定条件的行（联结条件）。

1.等值联结

如果where子句中的限定条件为具体字段是否相等，这种联结称为等值联结。

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
Where Vendors.vend_id =  Products.vend_id;

2.非等值联结

如果where子句中的限定条件是一个区间

SELECT last_name,salary,`grade`
FROM employees e,sal_grade g
WHERE e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
AND commission_pct IS NOT NULL;

3.笛卡尔积

如果不使用Where子句：

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

此时，第一个表中的每一行将与第二个表中的每一行配对，而不管逻辑上是否配在一起。这种联结方式，即笛卡尔积，也称交叉联结（cross join）。

笛卡尔集会在下面条件下产生:

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

1.2 内联接:[INNER] JOIN ON

内联接等同于等值联结，但语法稍微不同：

• 使用 JOIN 代替 FROM table1，table2
• 使用 ON 语句代替 WHERE子句

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER 
JOIN Products
ON Vendors.vend_id =  Products.vend_id;

1.3 联结多个表：Where子句

方法：

• 首先列出表名；
• 然后定义表之间关系。

SELECT vend_name, prod_name, prod_price，quantity
FROM Vendors, Products, OrderItems
Where Vendors.vend_id =  Products.vend_id
AND OrderItems.vend_id =  Products.vend_id
AND order_num = 20007；

联结的表越多，性能下降的越厉害。

2.创建高级联结

2.1 使用表别名

可以为列名、字段名、表名起别名，其目的是：

• 缩短SQL语句；
• 允许在一条 SELECT 语句中多次使用相同的表。

SELECT vend_name, prod_name, prod_price，quantity
FROM Vendors AS V, Products AS P, OrderItems AS O
Where V.vend_id =  P.vend_id
AND O.vend_id =  P.vend_id
AND order_num = 20007；

注意：

1）Oracle中不支持AS关键字，如要使用别名，则可以直接指定即可：

• Vendors V（而不是Vendors AS V）

2）表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户端。

2.2 使用不同类型的联结

1.自联结（self join）

方法：使用同一个表的不同别名，将相同表连接在一起

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones'；

一般情况下，使用自联结而不使用子查询：

• 许多DBMS处理联结远比处理子查询快得多。

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers
WHERE cust_name IN (SELECT cust_name 
						FROM Customers WHERE cust_contact= 'Jim Jones');

2.自然联结（nature join）

对于联结条件中的列，只返回唯一的一列。

方法：自己在SELECT语句中设定。

3.外联结（outer join）

为何使用：

• 当需要包含那些没有关联上的行的时候

包含：

• left outer join
• right outer join
• full outer join

Access、MySQL、SQLite 不支持 FULL OUTER JOIN

4.使用带聚集函数的联结

查询：每个顾客，及顾客的下单数

SELECT Customers.cust_id, COUNT(Orders_order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

总结：

1.使用正确的联结：

• 注意区分自联结、内联接inner join 、外联结 outer join、全外联结 full join，以及笛卡尔积 cross join

2.注意使用正确的联结语法：

• 应该查看具体的使用文档

3.一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型：

• 这样合法也很有用
• 但应该在一起测试他们之前，分别测试每个联结。
• 也可以使用联结进行反向筛选：左联结+右表中字段为NULL条件