Outer join help in oracle

I have:

select title, order#, STATE
from customers C, ORDERITEMS O, books b
where b.ISBN=O.ISBN (+)
order by title

But I'm trying to understand why rows that don't have order# have state

BODYBUILD IN 10 MINUTES A DAY   1003    CA
BODYBUILD IN 10 MINUTES A DAY   1003    WY
BO开发者_如何学JAVADYBUILD IN 10 MINUTES A DAY   1003    TX
BODYBUILD IN 10 MINUTES A DAY   1003    NY
BODYBUILD IN 10 MINUTES A DAY   1003    WA
BODYBUILD IN 10 MINUTES A DAY   1003    ID
BODYBUILD IN 10 MINUTES A DAY   1003    FL
BUILDING A CAR WITH TOOTHPICKS  -   FL
BUILDING A CAR WITH TOOTHPICKS  -   NJ
BUILDING A CAR WITH TOOTHPICKS  -   GA
BUILDING A CAR WITH TOOTHPICKS  -   MI

I find it a lot easier to use ANSI joins if more than one table is involved when doing outer joins:

SELECT title, order#, STATE
FROM customers c 
   JOIN orderitems O ON o.customer_id = c.id
   LEFT JOIN books b ON b.isbn = o.isbn
ORDER BY title

(this is essentially the same query that Arjan posted, just with standard syntax)

Depending on your needs you might want to use a LEFT JOIN for orderitems as well e.g. if you also want customers that do not have orderitems

It is also good practice to use the table alias for the columns in the select list as well. Doing that makes the statement easier to understand (because you immediately know from which table which column comes) and it's more stable against changes.

You are using 3 tables, but only one join. That will get you too many results. You will need two joins: probably somehow table Customers needs to be joined with OrderItems too.

Something like:

and o.customerId = c.id

Hence:

select b.title, o.order#, c.state
from customers c, orderitems o, books b
where o.customerId = c.id
and b.isbn = o.isbn (+)
order by title