SQL 连接多个表 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24100408/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining Multiple Tables - Oracle
提问by tworley1977
I'm studying multiple table joins this week and have some odd results being returned. Here is the scenario...
我本周正在研究多个表连接，并返回了一些奇怪的结果。这是场景...
Using the correct tables, create a query using the traditional join operation that will list the customer first and last name, book title, and order date (formatted as MM/DD/YYYY with an alias of “Order Date”) for all the customers who have purchased books published by 'PRINTING IS US'.
使用正确的表，使用传统连接操作创建查询，该查询将列出所有客户的姓名、书名和订单日期（格式为 MM/DD/YYYY，别名为“Order Date”）购买了“PRINTING IS US”出版的书籍的人。
With the database that I'm querying against, the correct tables for this query are BOOK_CUSTOMER, BOOKS, BOOK_ORDER, and PUBLISHER. The statement that I have written returns the information that I need, but it is returning almost 5900 records. I don't see how this can be right. The publisher, Printing is Us, only has 14 books listed in the database and there are only 20 customer records, so even if every customer purchased every Printing is Us book, that would only return 280 records total. Yet I can't figure out what I have wrong. My statement is listed below.
对于我查询的数据库，此查询的正确表是 BOOK_CUSTOMER、BOOKS、BOOK_ORDER 和 PUBLISHER。我写的语句返回了我需要的信息，但它返回了将近 5900 条记录。我不明白这怎么可能是对的。出版商“Printing is Us”在数据库中只列出了 14 本书，并且只有 20 条客户记录，因此即使每个客户购买了每本“Printing is Us”的书，总共也只会返回 280 条记录。然而我无法弄清楚我有什么错。我的声明如下。
SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date", p.publishername
FROM book_customer bc, books b, book_order bo, publisher p
WHERE(publishername = 'PRINTING IS US');
Anyone have any thoughts on what I'm missing here??
有人对我在这里想念的东西有任何想法吗？
Thanks.
谢谢。
回答by Bob Jarvis - Reinstate Monica
I recommend that you get in the habit, right now, of using ANSI-style joins, meaning you should use the INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOINelements in your SQL statements rather than using the "old-style" joins where all the tables are named together in the FROMclause and all the join conditions are put in the the WHEREclause. ANSI-style joins are easier to understand and less likely to be miswritten and/or misinterpreted than "old-style" joins.
我建议你的习惯，现在，使用ANSI风格的连接，这意味着你应该使用的INNER JOIN，LEFT OUTER JOIN，RIGHT OUTER JOIN，FULL OUTER JOIN，和CROSS JOIN在SQL语句中的元素，而不是使用“旧式”加入其中，所有的表都命名一起放在FROM子句中，所有连接条件都放在WHERE子句中。与“旧式”连接相比，ANSI 样式的连接更容易理解，并且不太可能被误写和/或误解。
I'd rewrite your query as:
我将您的查询重写为：
SELECT bc.firstname,
bc.lastname,
b.title,
TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date",
p.publishername
FROM BOOK_CUSTOMER bc
INNER JOIN books b
ON b.BOOK_ID = bc.BOOK_ID
INNER JOIN book_order bo
ON bo.BOOK_ID = b.BOOK_ID
INNER JOIN publisher p
ON p.PUBLISHER_ID = b.PUBLISHER_ID
WHERE p.publishername = 'PRINTING IS US';
Share and enjoy.
分享和享受。
回答by wvdz
You are doing a cartesian join. This means that if you wouldn't have even have the single where clause, the number of results you get would be book_customer size times books size times book_order size times publisher size.
您正在执行笛卡尔连接。这意味着，如果您甚至没有单个 where 子句，您获得的结果数量将是 book_customer 大小乘以书籍大小乘以 book_order 大小乘以出版商大小。
In order words, the result set gets blown up because you didn't add meaningful join clauses. Your correct query should look something like this:
换句话说，结果集被炸毁，因为您没有添加有意义的连接子句。您的正确查询应如下所示：
SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date", p.publishername
FROM book_customer bc, books b, book_order bo, publisher p
WHERE bc.book_id = b.book_id
AND bo.book_id = b.book_id
(etc.)
AND publishername = 'PRINTING IS US';
Note: usually it is adviced to not use the implicit joins like in this query, but use the INNER JOINsyntax. I am assuming however, that this syntax is used in your study material so I've left it in.
注意：通常建议不要像在这个查询中那样使用隐式连接，而是使用INNER JOIN语法。但是，我假设您的学习材料中使用了此语法，因此我将其保留了下来。
回答by Amir Sagiv
While former answer is absolutely correct, I prefer using the JOIN ONsyntax to be sure that I know how do I join and on what fields. It would look something like this:
虽然以前的答案是绝对正确的，但我更喜欢使用JOIN ON语法来确保我知道如何加入以及在哪些字段上。它看起来像这样：
SELECT bc.firstname, bc.lastname, b.title, TO_CHAR(bo.orderdate, 'MM/DD/YYYY') "Order Date", p.publishername
FROM books b
JOIN book_customer bc ON bc.costumer_id = b.book_id
LEFT JOIN book_order bo ON bo.book_id = b.book_id
(etc.)
WHERE b.publishername = 'PRINTING IS US';
This syntax seperates completely the WHEREclause from the JOINclause, making the statement more readable and easier for you to debug.
此语法将WHERE子句与JOIN子句完全分开，使语句更具可读性且更易于调试。
