关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联，所以才叫关系数据库。

联结 join

联结是一种机制，用来在一条 SELECT 语句中把多个表关联起来。

内联结 inner join/等值联结 equijoin

将两个表中有相同字段的行配对，有两种语法

-- 简单等值语法
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

-- 标准语法
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_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.prod_id
AND order_num = 20007;

自联结

通常用来代替从相同表中检索数据的子查询语句

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
-- c1, c2是相同的表
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
    AND c2.cust_contact = 'Jim Jones';

自然联结

外联结

包含在相关表中没有关联行的行

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

LEFT 代表左外联结，让 FROM 子句左边的表中所有行都被包含。换成 RIGHT 可以使用右外联结（SQLite 不支持）。另外直接调换表名的顺序也可以转换左右。

使用带聚集函数的联结

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;