Oracle where clause outer join

Question

I am having trouble understanding how to do an outer join using the where clause for a particular query. I can accomplish the join using the JOIN keyword. The query I am trying to accomplish is:
-- display a list of all books in the books table. if a book has been ordered by a customer also list the corresponding order number and state the customer is from

here is the table structure

    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) 

using join I can get it to display what I believe is the correct results with the following:

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 

this is what I have tried for my where clause join:

-- 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;

but when I do this query I get the following

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

Here is a link to the sql that will build the structure if needed https://www.dropbox.com/s/7tpbpz1hbufj3qn/JLDB_Build_8.sql

I am having a hard time figuring out what I am doing wrong/different with the where clause join. Any help or direction is appreciated. Thanks.

Answer 1

I found this after lots more digging

this is from http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle Database will return only the rows resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

The (+) operator does not produce an outer join if you specify one table in the outer query and the other table in an inner query.

I was able to get it working with the following:

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

Try this:

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

when using joins I recommend to do explicit left/right joins. For example..

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
 ...

In this case, the Table A records will be matched with the ones on Table B. If there´s no match on table B, then the columns from table B will be null. The second joins works loke the first one, this case will also show null if there is no matching value from field1 (on table A) in the Table C, fkfield1.

On the other hand, if you need to match all the records of the second table insteade of the first you need to do a "inner join". Just replace "left outer join" with "inner join" in the example above.

Thanks!

@leo