INNER JOIN
Databases & SQL Lesson 10 2:03 English narration · English + 中文 subtitles burned in
Chapters
Transcript
Splitting the data was the right thing to do, but now a question about customers and their orders touches both tables.
把数据拆开是对的，但现在一个关于"客户和他们的订单"的问题会同时碰到两张表。
A join puts them back together, and ON says how.
连接把它们重新拼回去，而 ON 说明怎么拼。
Each order carries a customer_id; follow that number to the customer row it names.
每一个订单都带着一个 customer_id；顺着那个数字找到它指名的那一行客户。
Three orders, three links — and two of them point at Ada.
三个订单，三条链接——而其中两条指向 Ada。
Each pair becomes one row of the result, with columns from both sides.
每一对配对变成结果里的一行，列来自两边。
That gives three rows — one joined row per pair — and Ada appears twice, because she has two orders.
于是得到三行——每一对配对一行—— 而 Ada 出现了两次，因为她有两个订单。
That surprises people, so say it plainly: a join does not give you one row per customer.
这会让人意外，所以直说：连接给你的不是"每个客户一行"。
It gives you one row per matching pair.
它给你的是"每一对匹配一行"。
And the other half of the definition.
还有定义的另一半。
Ben and Dan have no orders at all, so the join has nothing to pair them with — and they simply do not appear.
Ben 和 Dan 根本没有订单，所以连接没有东西可以和他们配对—— 于是他们干脆不出现。
That is what the word INNER means: keep a row only when the ON condition finds a match.
这就是 INNER 这个词的意思：只有当 ON 条件找到匹配时才保留一行。
Which is also a warning: do not count customers with a join like this one, because it silently counts only the customers who bought something.
这同时也是一个警告： 不要用这样的连接去数客户数量， 因为它会悄悄地只数那些买过东西的客户。
One piece of housekeeping.
还有一件杂务。
Once two tables are in play, a column name can be ambiguous — both of ours have an id.
一旦有两张表参与，一个列名就可能有歧义——我们这两张表都有 id。
So name the table first: customer dot id, orders dot total.
所以先写表名：customer.id，orders.total。
If you leave a bare id in there the database refuses it, and the error message is about an ambiguous column name.
如果你在里面留一个光秃秃的 id，数据库会拒绝它， 而报错信息说的就是"列名有歧义"。
Four things to take with you.
带走四点。
One: a JOIN pairs rows from two tables.
第一：连接把两张表的行配成对。
Two: ON names the key the two sides must match on.
第二：ON 指出两边必须匹配的那个键。
Three: INNER JOIN drops rows with no match on the other side.
第三：INNER JOIN 丢掉在另一边没有匹配的行。
Four: qualify a column as table dot column when both tables have it.
第四：当两张表都有某个列名时，写成"表名.列名"。
Now run the tasks below.
现在去做下面的题。