Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use oracle outer join with a filter where clause

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?

like image 767
Victor Avatar asked Aug 22 '13 20:08

Victor


People also ask

Can I use WHERE clause in outer join?

Introduction. Although an ON clause is required for each operation in an outer join in a FROM clause, an outer join can itself also include a WHERE clause. Any restriction in the WHERE clause is applied only to the table that is the final result of the outer join.

Does the WHERE clause run before or after the join?

Filtering in the WHERE clause If you move the same filter to the WHERE clause, you will notice that the filter happens after the tables are joined.

Can we use having and WHERE clause together in Oracle?

Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set.

Can you filter on a join in SQL?

A join filter allows a table to be filtered based on how a related table in the publication is filtered. Typically a parent table is filtered using a parameterized filter; then one or more join filters are defined in much the same way that you define a join between tables.


1 Answers

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.

like image 89
Justin Cave Avatar answered Oct 19 '22 05:10

Justin Cave