How to use oracle outer join with a filter where clause

Question

If i write a sql:

select * 
from a,b 
where     a.id=b.id(+) 
      and b.val="test"

and i want all records from a where corresponding record in b does not exist or it exists with val="test", is this the correct query?

Answer 1

You're much better off using the ANSI syntax

SELECT *
  FROM a
       LEFT OUTER JOIN b ON( a.id = b.id and
                             b.val = 'test' )

You can do the same thing using Oracle's syntax as well but it gets a bit hinkey

SELECT *
  FROM a, 
       b
 WHERE a.id = b.id(+)
   AND b.val(+) = 'test'

Note that in both cases, I'm ignoring the c table since you don't specify a join condition. And I'm assuming that you don't really want to join A to B and then generate a Cartesian product with C.

Answer 2

Move the condition into the JOIN clause and use the ANSI standard join pattern.

SELECT NameYourFields,...
FROM A
LEFT OUTER JOIN B
ON A.ID = B.ID
AND B.VAL = 'test'
INNER JOIN C
ON ...

Answer 3

A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

So you can do as follows :

SELECT
FROM a LEFT OUTER JOIN b ON a.id = b.id

--Note that you have used double quote "test" which is not used for varchar in SQL you should use single quote 'test'

AND b.val = 'test';

Answer 4

SELECT * FROM abc a, xyz b
 WHERE a.id = b.id
   AND b.val = 'test'