Allays return value from left table in join

Question

I have 2 tables A and B

A has cols(AKey, val1, val2) B has Cols(BKey,Akey, ValX, valY)

i have the following query

    select a.Val1,a.Val2,b.ValX
    from A
    Left Join B on a.AKey = b.Akey
    where a.Akey ={someValue}
    and ((b.valY ={aDifferentVal}) or (b.valY is NULL))

The situation is that i always want to return the values from table A. and this works when {aDifferentVal} exists in the the join, it also works when there are no values in table B for the Join, However when there are values in table be for the Join but none of these are {aDifferentVal} then the query return nothing, and i still want the values from table A.

How can i achieve this?

score 0 · Accepted Answer · answered Sep 03 '20 at 01:23

Just move the condition on the left joined table from the where clause to the on clause of the join - otherwise they become mandatory, and rows where they are not fullfilled are filered out (here this removes rows that match but whose valy does not match {adifferentval}):

select a.val1,a.val2,b.valx
from a
left join b 
    on  b.akey = a.akey
    and b.valy = {adifferentval}
where a.akey = {somevalue}

score 0 · Answer 2 · answered Sep 03 '20 at 01:24

Move the conditions on the second table to the on clause:

select a.Val1,a.Val2,b.ValX
from A Left Join
     B
     on a.AKey = b.Akey and (b.valY ={aDifferentVal})
where a.Akey = {someValue}

Filtering in the where clause (sort of) turns the outer join into an inner join. Your version is slightly better, because it is checking for NULL. However, the rows that match are:

The A values that have no match in B at all.
The A values that match the condition you specify.

What gets filtered out are A values that match a row in B but none of the matches have the condition you specify.

Allays return value from left table in join

2 Answers2