Left Outer join and an additional where clause

Question

Hi, I have a join on two tables defined as a left outer join so that all records are returned from the left hand table even if they don't have a record in the right hand table. However I also need to include a where clause on a field from the right-hand table, but.... I still want a row from the left-hand table to be returned for each record in the left-hand table even if the condition in the where clause isn't met. Is there a way of doing this?

Answer 1

Yes, put the condition (called a predicate) in the join conditions

   Select [stuff]
   From TableA a
       Left Join TableB b
           On b.Pk = a.Pk
               -- [Put your condition here, like this]
               And b.Column = somevalue

Answer 2

You just need to put the predicate into the JOIN condition. Putting it into the WHERE clause would effectively convert your query to an inner join.

For Example:

...
From a
Left Join b on a.id = b.id and b.condition = 'x'

Answer 3

SELECT x.fieldA, y.fieldB
FROM x
LEFT OUTER JOIN (select fieldb, fieldc from Y where condition = some_condition)
ON x.fieldc = y.fieldc

Answer 4

select * 
  from table1 t1 
  left outer join table2 t2 on t1.id = t2.id
 where t1.some_field = nvl(t2.some_field, t1.some_field) 

UPD: errr... no. this way:

select * 
  from table1 t1 
  left outer join table2 t2 on t1.id = t2.id
 where some_required_value = nvl(t2.some_field, some_required_value) 

nvl is an Oracle syntax which replaces first argument with second in case it is null (which is common for outer joins). You can use ifnull or coalesce for other databases.

Thus, you compare t2.some_field with your search criteria if it has met join predicate, but if it has not, then you just return row from table1, because some_required_value compared to itself will always be true (unless it is null, however - null = null yields null, neither true not false.

Answer 5

You can use

WHERE (right_table.column=value OR right_table.column IS NULL)

This will return all rows from table 1 and table 2, but only where table 1 does not have a corresponding row in table 2 or the corresponding row in table 2 matches your criteria.