CODEWITHSUNDEEP
t-sql
jeroenh
jeroenh

Reputation: 26782

select record from joined table if it exists

I'm working on a sql query that should 'coalesce' the records from 2 tables, i.e. if the record exists in table2, it should take that one, otherwise it should fall back to the values in table1.

In the example, table1 and table2 have just 2 fields (id an description), but obviously in reality there could be more.

Here's a small test case:

create table table1 (id int, description nvarchar(50))
create table table2 (id int, description nvarchar(50))

insert into table1 values (1, 'record 1')
insert into table1 values (2, 'record 2')
insert into table1 values (3, 'record 3')

insert into table2 values (1, 'record 1 modified')
insert into table2 values (2, null)

The result of the query should look like this:

1, "record 1 modified"
2, null
3, "record 3"

Here's what I came up with. 

select 
  case when table2.id is not null then 
      table2.id else table1.id 
  end as Id,
  case when table2.id is not null then 
      table2.description 
  else 
      table1.description 
  end as Description
    -- etc for other fields
from table1
left join table2 on table1.id = table2.id

Is there a better way to achieve what I want? I don't think I can use coalesce since that would not select a null value from table2 if the corresponding value in table1 is not null.

Upvotes: 2

Views: 536

Answers (2)

Dan
Dan

Reputation: 45752

Here's an alternative:

SELECT table2.*
FROM table1
    RIGHT JOIN table2
        ON table1.id = table2.id

UNION

SELECT table1.*
FROM table1
    FULL OUTER join table2
        ON table1.id = table2.id
WHERE table1.id NOT IN (SELECT id FROM table2)
--and table2.id not in (select id from table1)

You can add in that last line if you don't want ids that are only in table2. Otherwise I guess Stuart Ainsworth's solution is better (i.e. drop all the joins)

http://sqlfiddle.com/#!3/03bab/12/0

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

How about:

SELECT t2.ID, t2.Description
FROM table2 t2
UNION ALL
SELECT t1.ID, t1.Description
FROM table1 t1
WHERE NOT EXISTS (SELECT * 
                  FROM table2 t2
                  WHERE t2.ID = t1.ID)

The above query gets all the records from table 2 (including the case where description is NULL but the ID is populated), and only the records from table 1 where they don't exist in table 2.

Upvotes: 2

Related Questions