我很难理解如何使用where子句进行特定查询的外部连接。我可以使用join关键字来完成连接。我想要完成的查询是:
——显示图书表中所有图书的列表。如果有客户订购了一本书，也要列出相应的订单号，并说明客户来自
下面是表结构
desc customers
Name Null Type
--------- -------- ------------
CUSTOMER# NOT NULL NUMBER(4)
LASTNAME NOT NULL VARCHAR2(10)
FIRSTNAME NOT NULL VARCHAR2(10)
ADDRESS VARCHAR2(20)
CITY VARCHAR2(12)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
REFERRED NUMBER(4)
REGION CHAR(2)
EMAIL VARCHAR2(30)
desc orders
Name Null Type
---------- -------- ------------
ORDER# NOT NULL NUMBER(4)
CUSTOMER# NUMBER(4)
ORDERDATE NOT NULL DATE
SHIPDATE DATE
SHIPSTREET VARCHAR2(18)
SHIPCITY VARCHAR2(15)
SHIPSTATE VARCHAR2(2)
SHIPZIP VARCHAR2(5)
SHIPCOST NUMBER(4,2)
desc orderitems
Name Null Type
-------- -------- ------------
ORDER# NOT NULL NUMBER(4)
ITEM# NOT NULL NUMBER(2)
ISBN VARCHAR2(10)
QUANTITY NOT NULL NUMBER(3)
PAIDEACH NOT NULL NUMBER(5,2)
desc books
Name Null Type
-------- -------- ------------
ISBN NOT NULL VARCHAR2(10)
TITLE VARCHAR2(30)
PUBDATE DATE
PUBID NUMBER(2)
COST NUMBER(5,2)
RETAIL NUMBER(5,2)
DISCOUNT NUMBER(4,2)
CATEGORY VARCHAR2(12)
使用join，我可以让它显示我认为正确的结果:
SELECT b.title, c.state, order#
FROM customers c JOIN orders o USING (customer#)
JOIN orderitems oi USING (order#)
RIGHT OUTER JOIN books b USING (isbn);
TITLE STATE ORDER#
------------------------------ ----- ----------
HOW TO GET FASTER PIZZA
THE WOK WAY TO COOK
REVENGE OF MICKEY MI 1012
REVENGE OF MICKEY GA 1019
REVENGE OF MICKEY WA 1009
REVENGE OF MICKEY TX 1014
BODYBUILD IN 10 MINUTES A DAY FL 1003
HANDCRANKED COMPUTERS MI 1012
SHORTEST POEMS GA 1005
PAINLESS CHILD-REARING GA 1001
PAINLESS CHILD-REARING NJ 1004
PAINLESS CHILD-REARING FL 1016
PAINLESS CHILD-REARING MI 1012
PAINLESS CHILD-REARING GA 1011
COOKING WITH MUSHROOMS WY 1020
COOKING WITH MUSHROOMS ID 1008
COOKING WITH MUSHROOMS FL 1003
COOKING WITH MUSHROOMS WA 1000
COOKING WITH MUSHROOMS WA 1009
COOKING WITH MUSHROOMS FL 1018
COOKING WITH MUSHROOMS NJ 1015
HOLY GRAIL OF ORACLE TX 1007
BUILDING A CAR WITH TOOTHPICKS
BIG BEAR AND LITTLE DOVE FL 1017
BIG BEAR AND LITTLE DOVE TX 1007
BIG BEAR AND LITTLE DOVE MI 1012
DATABASE IMPLEMENTATION IL 1002
DATABASE IMPLEMENTATION TX 1007
DATABASE IMPLEMENTATION FL 1003
DATABASE IMPLEMENTATION WY 1013
DATABASE IMPLEMENTATION FL 1018
DATABASE IMPLEMENTATION NJ 1010
HOW TO MANAGE THE MANAGER GA 1001
E-BUSINESS THE EASY WAY TX 1007
E-BUSINESS THE EASY WAY FL 1006
35 rows selected
这是我尝试的where子句连接:
-- using where clause
SELECT b.title, c.state, oi.order#
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# = o.customer#
AND o.order# = oi.order#
AND oi.isbn(+) = b.isbn;
但是当我执行这个查询时，我得到以下
TITLE STATE ORDER#
------------------------------ ----- ----------
BODYBUILD IN 10 MINUTES A DAY FL 1003
REVENGE OF MICKEY GA 1019
REVENGE OF MICKEY TX 1014
REVENGE OF MICKEY MI 1012
REVENGE OF MICKEY WA 1009
DATABASE IMPLEMENTATION FL 1018
DATABASE IMPLEMENTATION WY 1013
DATABASE IMPLEMENTATION NJ 1010
DATABASE IMPLEMENTATION TX 1007
DATABASE IMPLEMENTATION FL 1003
DATABASE IMPLEMENTATION IL 1002
COOKING WITH MUSHROOMS WY 1020
COOKING WITH MUSHROOMS FL 1018
COOKING WITH MUSHROOMS NJ 1015
COOKING WITH MUSHROOMS WA 1009
COOKING WITH MUSHROOMS ID 1008
COOKING WITH MUSHROOMS FL 1003
COOKING WITH MUSHROOMS WA 1000
HOLY GRAIL OF ORACLE TX 1007
HANDCRANKED COMPUTERS MI 1012
E-BUSINESS THE EASY WAY TX 1007
E-BUSINESS THE EASY WAY FL 1006
PAINLESS CHILD-REARING FL 1016
PAINLESS CHILD-REARING MI 1012
PAINLESS CHILD-REARING GA 1011
PAINLESS CHILD-REARING NJ 1004
PAINLESS CHILD-REARING GA 1001
BIG BEAR AND LITTLE DOVE FL 1017
BIG BEAR AND LITTLE DOVE MI 1012
BIG BEAR AND LITTLE DOVE TX 1007
HOW TO MANAGE THE MANAGER GA 1001
SHORTEST POEMS GA 1005
32 rows selected
这里是一个sql的链接，如果需要，它将构建结构https://www.dropbox.com/s/7tpbpz1hbufj3qn/JLDB_Build_8.sql
我很难弄清楚我在哪里做错了/不同的子句加入。任何帮助或指导都是感激的。谢谢。
当使用连接时，我建议进行显式的左/右连接。例如. .
Select A.*, B.*, C.*
from TableA A
left outer join TableB B
on A.field1 = B.fkfield1
and A.field2 0 B.fkfield2
...
left outer join TableC C
on A.field1 = C.fkfield1
and A.field2 0 C.fkfield2
...
在这种情况下，表A的记录将与表B中的记录匹配。如果表B上没有匹配，则表B中的列将为空。第二个连接的工作方式与第一个连接相同，如果表C中的fkfield1(在表A上)没有匹配的值，那么这种情况下也将显示null。
另一方面，如果您需要匹配第二个表而不是第一个表的所有记录，则需要执行"内连接"。只需将上面例子中的"左外连接"替换为"内连接"。
谢谢!
@leo
试试这个:
SELECT b.title, o.state, o.order#
FROM books b
, (select o.order#, oi.isbn, o.customer#, c.state
from orders o, orderitems oi, customers c
where o.order# = oi.order#
and c.customer# = o.customer#
) o
WHERE
AND o.isbn(+) = b.isbn;
我发现了这个
这是来自http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm
如果A和B由多个连接条件连接，则必须使用所有这些条件中的(+)运算符。如果你不知道，那么Oracle数据库将只返回简单操作的结果行加入，但没有警告或错误提示你不要加入具有外部连接的结果。
如果指定了外连接，则(+)操作符不会产生外连接表在外部查询中，另一个表在内部查询中。
我可以让它工作，使用以下命令:
SELECT b.title, c.state, o.order#
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer#(+) = o.customer#
AND o.order#(+) = oi.order#
AND oi.isbn(+) = b.isbn;