INNER JOIN · 内连接 INNER JOIN
Joining two tables
To use data from both tables at once, you join them. An INNER JOIN pairs up rows that match on a key — here, the order's customer_id with the customer's id:
When a column name could come from either table, write table.column so it is clear.
连接两张表
要同时使用两张表的数据,就把它们连接起来。INNER JOIN 把在某个键上匹配的行配成一对 —— 这里是订单的 customer_id 和顾客的 id:
SELECT customer.name, orders.total
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id;
当一个列名两张表都可能有时,写成 table.column,这样更清楚。
Only matching rows survive
An INNER JOIN keeps a row only when the ON condition finds a match in the other table.
In our shop, Ben and Dan have no orders, so they do not appear in the join — there is nothing to pair them with. Ada and Cara, who do have orders, appear once for each of their orders.
只有匹配的行会保留
INNER JOIN 只在 ON 条件能在另一张表里找到匹配时,才保留这一行。
在我们的商店里,Ben 和 Dan 没有订单,所以他们不会出现在连接结果里 —— 没有东西能和他们配对。有订单的 Ada 和 Cara,则会按其订单数各出现若干次。
Common mistakes
- Match on the key with
ON; a missingONpairs every row with every row. - INNER JOIN drops rows with no match on the other side.
常见错误
- 用
ON按键匹配;没有ON会把每行和每行配对。 - INNER JOIN 丢掉在另一侧没有匹配的行。
INNER JOIN matches keys · INNER JOIN 按键匹配
Rows from two tables combine where the key matches. · 两个表的行在键匹配处合并。
List each customer's name next to the total of each of their orders. Join customer to · 到 orders on the key, ordered by orders.id. · 把每位顾客的 name 和他们每张订单的 total 并排列出。在键上把 customer 连接到 orders,按 orders.id 排序。
Click Run to see the output here. · 点击“运行”查看此处输出。
Show the customer name and order total for orders of 25 or more, largest total first. · 显示金额在 25 或以上的订单的顾客 name 和订单 total,金额最大的排最前。
Click Run to see the output here. · 点击“运行”查看此处输出。