select if is null

Question

I need to make the inner join of two tables, however, wanted to know how can I resolve this situation:

If the field in the second table is NULL he do inner join with another field.

Can you give me an example?

Thanks

more information please - difficult to say what to do with the NULL without know what you want to achieve? — whytheq, Mar 19 '13 at 20:20
try using coalesce: `select TableA.Col1, TableB.Col2 From TableA INNER JOIN TableB on TableA.JoinColumn=coalesce(TableB.JoinCoumn1, TableB.JoinColum2, TableB.JoinColumn3......and so on)` That will consider TableB.JoinColumn1, then 2, then 3, halting at the first non-null column value. — Stephen Byrne, Mar 19 '13 at 23:46
@whytheq I think I explained well! and cant understanda the downvotes — PookPook, Mar 20 '13 at 10:36
@StephenByrne I used an inner join with the "case when ..." because coalesce is not working for me. — PookPook, Mar 20 '13 at 10:38
@sh0rt6r we could do with seeing what the initial data looks like; we could also do with see an example of the result set you are trying to achieve — whytheq, Mar 20 '13 at 11:23
@sh0rt6r looks at this question: [**STACK OVERFLOW**](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) ; it is very clear what they are starting with and what they are trying to achieve. If you make you question more clear then the down-votes will probably disappear — whytheq, Mar 20 '13 at 11:28

whytheq · Accepted Answer · 2013-03-19T20:24:32.610

depends what you are trying to achieve...

CREATE TABLE #X 
(
[Id] INT,
[Name] VARCHAR(100)
)
INSERT INTO #X VALUES
(1,'michaeljackson'),
(2,'jim'),
(3,'jill'),
(4,'j')

CREATE TABLE #Y 
(
[Id] INT,
[AlternateId] INT,
[Score] INT
)
INSERT INTO #Y VALUES
(1,1,10),
(2,2,20),
(3,3,30),
(4,4,40),
(NULL,2,50)

--will join all of the records in #x to the null record
SELECT *
FROM   #X x
       INNER JOIN #Y y
       ON 
       x.Id = COALESCE(y.Id,x.Id)

--will join just to the ID = 4 record
SELECT *
FROM   #X x
       INNER JOIN #Y y
       ON 
       x.Id = COALESCE(y.Id,4)


--redirect and let join use alternative field
SELECT *
FROM   #X x
       INNER JOIN #Y y
       ON 
       x.Id = COALESCE(y.Id,y.AlternateId)

--maybe you want to actually do a FULL OUTER JOIN!
SELECT  *
FROM    #X x
    FULL OUTER JOIN #Y y
    ON 
    x.Id = y.Id

I used an inner join with the "case when ..." because coalesce is not working for me. — PookPook, Mar 20 '13 at 10:38
I actually created a further question to explore differences between `CASE/COALESCE` [**Stackoverflow question here**](http://stackoverflow.com/questions/15523000/coalesce-or-case-more-efficient-and-or-standard) — whytheq, Mar 20 '13 at 12:24

score 1 · Answer 2 · answered Mar 19 '13 at 18:29

1

Easiest is probalby to consider the second table as two disjoint tables like this:

select *
from a
join (
    select NewKey = key1, * from b where b.key1 is null
  union all
    select NewKey = key2, * from b where b.key1 is not null
) b on b.NewKey = a.key

answered Mar 19 '13 at 18:29

Pieter Geerkens

11,775
2
32
52

thanks for the reply. not tested because it could solve with using CASE WHEN – PookPook Mar 20 '13 at 11:07

score 1 · Answer 3 · answered Mar 19 '13 at 18:31

1

I believe the following should work adequately.

SELECT *
  FROM tblA a
    INNER JOIN tblB b ON (b.Col1 IS NOT NULL AND b.Col1 = a.Col1) OR (b.Col1 IS NULL AND b.Col2 = a.Col2)

If b.Col1 is not NULL then it joins on Col1, otherwise if it is NULL then it joins on Col2.

answered Mar 19 '13 at 18:31

Martin

16,093
1
29
48

thanks for the reply. not tested because it could solve with using CASE WHEN – PookPook Mar 20 '13 at 11:08

score 1 · Answer 4 · answered Mar 19 '13 at 18:51

1

Try this:

    select * from a 
    inner join b on 
    (case when b.columntojoin is null then b.alternatecolumn else b.columntojoin end) 
    = a.columntojoin

answered Mar 19 '13 at 18:51

rplusm

33
5

select if is null

4 Answers4