Victor Victor - 8 months ago 33
SQL Question

How to use oracle outer join with a filter where clause

If i write a sql:

select *
from a,b
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?


You're much better off using the ANSI syntax

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

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

  FROM a, 
   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.