Oracle where子句外连接

Question

我在理解如何使用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的链接，该sql将在需要时构建结构https://www.dropbox.com/s/7tpbpz1hbufj3qn/JLDB_Build_8.sql

我很难弄清楚我在where子句连接中做错了什么。任何帮助或指导都是非常感谢的。谢谢。

Answer 1

我在深入挖掘之后发现了这个

这是来自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;

Answer 2

试试这个：

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;

Answer 3

在使用joins时，我建议使用显式的左/右joins。例如..

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中的列将为null。第二个joins与第一个相似，如果在表C，fkfield1中没有来自field1 (在表A上)的匹配值，这种情况也会显示null。

另一方面，如果您需要匹配第二个表中的所有记录，而不是第一个表中的所有记录，则需要执行“内连接”。只需将上面示例中的“左外部联接”替换为“内联接”即可。

谢谢!

@leo