Re: Simple How To: SQL left Join with and clause

From: kibeha <kibeha_at_post6.tele.dk>
Date: 25 Feb 2004 01:42:32 -0800
Message-ID: <444b180d.0402250142.7293fdea@posting.google.com>

joes_at_bluewin.ch (joes) wrote in message news:<26760a3e.0402240024.cb53279_at_posting.google.com>...
> SELECT * FROM client, order WEHER client.id = order.clientid(+)
> and order.price < 10.0

Your statement means the following :

• "client.id = order.clientid(+)" That means "give me orders having clientid = client.id and if such orders cannot be found, then give me a fictive orders record containing null values".
• "order.price < 10.0" That means "give me only records where order.price is less than 10", but the "fictive" records with null values will not match this statement ! Null values will not compare to anything except using "IS NULL".

What you have effectively done is to make your query identical to this one :

SELECT * FROM client, order
WHERE client.id = order.clientid
AND order.price < 10.0

That is, you have negated the effect of the outer join completely.

So you can do as suggested :

SELECT * FROM client, order
WHERE client.id = order.clientid(+)
AND (order.price < 10.0 OR order.price IS NULL)

But an easier way is to to this :

SELECT * FROM client, order
WHERE order.clientid(+) = client.id
AND order.price(+) < 10.0

By putting (+) on BOTH predicates, you will get the outer join you expect.

/KiBeHa Received on Wed Feb 25 2004 - 03:42:32 CST