Left Outer join and an additional where clause

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 开发者_JAVA技巧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?

---

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

The reason this works is because the query processor applies conditions in a where clause after all joins are completed, and the final result set has been constructed. So, at that point, a column from the a table on the outer side of a join that has null in a a column you have established a predicate on will be excluded.

Predicates in a join clause are applied before the two result sets are "joined". At this point all the rows on both sides of the join are still there, so the predicate is effective.

---

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'

---

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.

---

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

---

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.