LEFT OUTER JOIN - Return a constant value instead of NULL for mismatches

Question

Post reply

LEFT OUTER JOIN - Return a constant value instead of NULL for mismatches

maneesh_menon

SSC Enthusiast

Points: 188
More actions
November 21, 2009 at 8:46 am

#206699

LEFT OUTER JOIN returns each row that satisfies the join of the first table with the second table.
It also returns any rows from the first table that had no matching rows in the second table.
The nonmatching rows in the second table are returned as null values.
Is it possible that instead of returning non matching as NULL, system returns value 1.
Even if table B's row does not match, for one specific column, instead of NULL I want system
to return the value 1 for that column, if the column's original value is say ... greater than 1000
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 1

select a.col1, isnull(b.colX,1) as colX

from myobject a

left join myotherobject b

on b.fkcol = a.keycol

have fun .

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

maneesh_menon SSC Enthusiast Points: 188 More actions · Answer 2

hey thanks.

I shall try out isnull tomorrow in office.

Isnull will check the returned value is null or not.

what is I have to check two conditions.

I need to check whether the returned value is NULL and also whether the original value in the column was null or not.

Is there a way I can add a case statement to the following ... Isnull(table.col,1) ... to check that aslo

Thanks.

maneesh_menon SSC Enthusiast Points: 188 More actions · Answer 3

hi ALZDBA,

Let me make my requirement more specfic.

I have to perform left outer join between table a and table b.

now we know that there can be rows in 'table b' for which comparison do not match in the ON clause.

in my application, I want to find out whether the table B' col1 did not match because

the value was in itself greater than 1000.

In that case only I want to override with value 1.

So the query's result will have three types of values returned in tableB.col1

a. 'actual value' because lett outer join condition matched

b. 'null' because left outer join condition did not match

c. 'actual value' because though condition did not match, the original value was greatet than 1000

Thanks,

maneesh

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 4

off course you can add the extra case to evaluate the > 1000.

Keep in mind to actually evaluate on one of the join condition key columns of your B table. Because they will only be null if your condition didn't match.

It is possible your row non-key column(s) IS actually NULL. Do you want to replace that NULL also with a replacement value ??

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

maneesh_menon SSC Enthusiast Points: 188 More actions · Answer 5

I guess i was going in the wrong direction totally.

say there are three rows in table B whose col1 value is greater than 1000.

these three rows in table B will never be retrieved by left outer join because condition in the

on clause will never match between table A and table B.

I was thinking there must be some way I can retrieve additional information from table B ... by additional I mean the thee rows which do not meet the criteria on ON CLAUSE but its value is greate than 1000.

I might have to use an 'OR CLAUSE' in the 'ON CONDITION' which can hamper performance.

Regards,

Maneesh

vliet SSCommitted Points: 1998 More actions · Answer 6

If you need all the rows from table A with (optional) matching rows from table B and also certain rows from table B you could use a full outer join:

select isnull( A.col1 , B.col1 ) as result

from A full outer join B on A.col1 = B.col1

where ( A.col1 is not null or B.col1 > 1000 )

This statement will return all rows from table A that have a value in col1 and all rows from table B with a value in col1 above 1000 that does not exist in table A. If col1 may be null in table A you might consider replacing it in the where-clause only with another column of table A that does not contain nulls, for example a primary key column.