Outer joins problem 1

[knacath]

Hi,

I have two tables left outer joined. Without a whereclause I can see my null rows from the right table. When I insert a where clause on a field in the right table, the outer join is negated, and the output appears as if the tables were equijoined. This is for AS/400.

Is this normal, and is there a workaround in SQL? Thanks.

 

[tlbroadbent]

Try testing the column for null as well as the other value.

Select a.col1, a.col2, b.col3, b.col4, ...
From tableA As a left join tableB as b
on a.key = b.key
Where b.colX='value'
Or b.colX Is Null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[knacath]

Thanks Terry, seems to work, though I don't understand the logic!

b.colx was not bringing in the null values, so I would've thought we should condition for null on the fields that don't come in instead of b.colx.

 

[knacath]

I was not very clear in the previous post. B.colx is not among the joined fields, that is what I was trying to say.

 

[tlbroadbent]

WIth the LEFT JOIN, you want to select all records from A whether there is a match in B or not. When there is no matchin record in B, the values for all columns in B are NULL. So you can test any column for NULL. However, if the column you test is nullable, this query will return incorrect results.

I chose the same column you were already testing. However, I would normally check if the key column is NULL because it should not be nullable.

Where b.colX='value' Or b.key Is Null Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.