SQL基础-联接(join)

理解关系表和关系表的设计。设计关系表的时候，将信息分散到多个表中，避免在一个表中存在太多重复信息。关系表能更好地扩展。

当信息存在多个表里时，如何在一条查询语句里获取来自不同表中的信息。
join就是一种机制，在同一个条查询语句中关联多个表中的信息。

从关系运算的角度来说，连接运算是从两个关系的笛卡尔积中选取满足条件的元组。
一般连接：从两个关系的笛卡尔积中选取属性间符合一定条件的元组
等值连接：属性间的条件为相等
自然连接：属性间的条件为相等，去掉结果属性列中的重复项
外连接：在一般连接的基础上，选取未匹配到的行。左连接即选取左边未匹配的行，右边相应的值设为空，右连接同理。

1、Inner Join

基于两个表之间的相等性测试的连接，这种连接称为内连接。

SELECT ta.iten1, tb.item2, tc.item3
FORM table_a AS ta, table_b AS tb, table_c AS tc
WHERE ta.tb_id = tb.id 
AND tb.tc_id = tc.id

等价于

SELECT ta.iten1, tb.item2, tc.item3
FORM table_a AS ta INNER JOIN table_b AS tb INNER JOIN table_c AS tc
ON ta.tb_id = tb.id 
AND tb.tc_id = tc.id

2、Self Join

用来代替子查询的方式
假设一个场景：某条数据库中的数据包含字段item1,item2。其中item1的值为text1,item2的值为text2;现要查询数据中所有与item1=text1时对应的text2相等的数据。
用子查询的方式：

SELECT t1.item1, t1.item2 FROM database_demo AS t1 WHERE t1.item2 = (SELECT t2.item2 FROM database_demo AS t2 WHERE t2.item1 = 'text1' )

改用自联结的方式为：

SELECT t1.item1, t1.item2 FROM database_demo AS t1, database_demo AS t2 WHERE t1.item2 = t2.item2 AND t2.item1 = 'text1'

自连接通常用于替换使用子查询的语句，这些子查询从与外部语句相同的表中检索数据。尽管最终结果是相同的，但是许多dbms进程的连接速度要比它们执行子查询快得多。通常值得尝试这两种方法来确定哪一种性能更好。

3、Nature Join

自然连接只是消除了那些多次出现的情况，因此每个列只返回一个。进行比较的属性必须是相同的属性，比较的条件也是相等条件。
自然连接是只选择唯一列的连接。这通常在一个表上使用通配符完成
(SELECT *)，并显示列出所有其他表的列的子集。

SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, 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_id = 'RGAN01';

在本例中，通配符仅用于第一个表,所有其他列都被显式列出，这样就不会检索到重复的列。

事实是，到目前为止您所创建的每个内部连接实际上都是一个自然连接，您可能永远都不需要一个不是自然连接的内部连接。

4、Outer Joins

大多数连接将一个表中的行与另一个表中的行关联起来。但有时，您希望包含没有相关行的行。
假设有如下场景：

• 计算每个客户下了多少订单，包括尚未下订单的客户。
• 列出所有产品和产品的订购数量，包括没有任何人订购的产品。
• 计算平均销售规模，把尚未下订单的客户也考虑进来。
  在上面的使用场景中，联接包括了在相关的表中没有关联的行，这种类型的联接被叫做外联接。

下面的查询语句是一个简单的内部联接。

SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

查出的结果：

改用LEFT OUTER JOIN
检索所有客户的列表，包括那些没有下订单的客户

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

与将两个表中的行关联起来的内部连接不同，外部连接还包括没有关联行的行。在使用外部连接时，必须使用RIGHT或
LEFT关键字指定包含所有行的表(RIGHT为外连接右边表的行，LEFT为左的行)。

请记住，外部连接总是有两种基本形式—左外部连接和右外部连接。它们之间唯一的区别是它们所关联的表的顺序。换句话说，只需反转FROM或WHERE子句中的表顺序，左外连接就可以转换为右外连接。因此，这两种类型的外部连接可以互换使用，关于使用哪一种连接的决定纯粹是基于方便。

外部连接还有另一种变体，即完整的外部连接，它从两个表中检索所有行，并将可能相关的行关联起来。左外连接或右外连接包含来自单个表的不相关行，而完整的外连接包含来自两个表的不相关行。完整外部连接的语法如下

SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;

MariaDB, MySQL, Open Office Base，或SQLite等不支持FULL OUTER JOIN

5、使用联接和聚合函数