JOIN 连接两张表

第 11 课，共 21 课 · 第 3 部分 · 多张表 · 约 15 分钟

订单表里记着每个订单是哪个客户下的，可只记了客户的编号：

1SELECT * FROM orders ORDER BY id;

4 号订单是 customer_id 为 4 的客户下的。4 号客户叫什么、住哪儿？要去客户表里找。这一课学怎么一条查询同时用两张表。

为什么要分好几张表

把客户的名字和城市直接写进每个订单不行吗？行，可是 1 号客户下了 3 单，她的名字、城市就要写 3 遍。哪天她搬家了，3 个地方都得改，漏改一个，数据就自相矛盾了。

所以数据库通常这样存：客户的信息只在客户表里写一次，订单里只写客户的编号。用的时候再按编号把两张表连接起来。

JOIN … ON

FROM 表一 JOIN 表二 ON 条件：把两张表的行一对一对配起来，ON 说怎么配。这里是订单的 customer_id 等于客户的 id：

1SELECT orders.id, orders.order_date, customers.name2FROM orders3JOIN customers ON orders.customer_id = customers.id4ORDER BY orders.id;

两张表都有的列名（比如 id）要写成 表名.列名，说清是哪张表的。

点单步看看 JOIN 那一步：每个订单拿着自己的 customer_id 去客户表里找 id 一样的那一行，找到了就拼成一行。12 个订单，拼出 12 行。

JOIN 也可以写成 INNER JOIN，意思完全一样。

表的别名

表名每次都写全太长。在 FROM 和 JOIN 里给表起个短名字，后面就用短名字：

1SELECT o.id, o.order_date, c.name, c.city2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4ORDER BY o.id;

orders AS o 的意思是「orders 表，下面叫它 o」。AS 也可以省掉，写成 orders o，意思一样。

同名的列

只在一张表里有的列，比如 order_date、name，可以不写前面的表名。可两张表都有的列不行：

1SELECT id, name, order_date2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id;

ERROR 1052 (23000): Column 'id' in field list is ambiguous

ERROR 1052：id 这一列「不明确」。订单表有 id，客户表也有 id，数据库不知道你要哪个。写成 o.id 或 c.id 就好。

小技巧： 连接查询里，每一列都写上表的别名（o.id、c.name），哪怕不写也不会报错。读的人一眼就知道每一列从哪儿来。

配不上的行没了

反过来，从客户这边连接订单：

1SELECT c.id, c.name, o.id AS order_id2FROM customers AS c3JOIN orders AS o ON o.customer_id = c.id4ORDER BY c.id, o.id;

再点单步看看配对：1 号客户配上了 3 个订单，变成 3 行；6、9、10 号客户在订单表里一个订单都找不到，配不上，就没了，结果里根本没有他们。

JOIN 只留下两边都配上的。要把没下过单的客户也留下，得用第 12 课的 LEFT JOIN。

1SELECT p.name, oi.order_id2FROM products AS p3JOIN order_items AS oi ON oi.product_id = p.id4ORDER BY oi.order_id, p.id;

JOIN 加上 WHERE

连接好的结果和一张普通的表一样，后面照样可以写 WHERE、ORDER BY。上海的客户下过哪些订单？

1SELECT o.id, o.order_date, c.name2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4WHERE c.city = '上海'5ORDER BY o.id;

city 在客户表里，order_date 在订单表里，连接以后一行里两样都有，WHERE 可以用任何一张表的列。

订单里买了什么

订单明细表只有商品编号。连接商品表，就能看到 3 号订单买的是什么：

1SELECT p.name, oi.quantity, oi.unit_price2FROM order_items AS oi3JOIN products AS p ON oi.product_id = p.id4WHERE oi.order_id = 35ORDER BY p.id;

三张表一起连接（订单 → 明细 → 商品），第 13 课再讲。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

1SELECT id, order_date, customer_id2FROM orders3WHERE status = '待发货';

1SELECT order_id, product_id, quantity FROM order_items;

1SELECT id, name, order_date2FROM orders AS o3JOIN customers AS c ON o.customer_id = c.id4WHERE city = '广州'5ORDER BY id;

这一课学会了

• 数据分在几张表里，每样信息只写一次，表之间用编号联系。
• FROM 表一 JOIN 表二 ON 条件 把两边配得上的行拼成一行；JOIN 就是 INNER JOIN。
• 给表起别名（orders AS o），列写成 o.id；两张表都有的列不写表名会得到 ERROR 1052。
• 配不上的行不会出现在结果里；要留下它们，用第 12 课的 LEFT JOIN。
• 连接后的结果照样可以用 WHERE、ORDER BY。