Select data from 2 tables conditionally

Let’s say I have 2 tables (Table1 and Table2) with same structure

• int Id
• string Name

Ex:

Table1: (1,’abc’), (2,’xxx’), (3, ‘mm’)

Table2: (1,’abcd’), (3, ‘nnnn’)

The goal is to display data from Table1 if there is no data for the same Id in Table2 or from Table2 when there is such data

For the tables above, the data returned will be:

(1, ‘abcd’) — from Table2

(2, ‘xxx’) — from Table1

(3, ‘nnnn’) — from Table2

This is what I tried:

select Table1.Id, 
case Table2.Name
    when null then Table1.Name
    else Table2.Name 
end as Name
from Table1
left join Table2 on Table2.Id = Table1.Id

but doesn’t work

>Solution :

Your CASE WHEN is somewhat wrong, you need IS to check for NULL

CREATE TABLE Table1(id int, name varchar(5))

CREATE TABLE Table2(id int, name varchar(5))

INSERT INTO Table1 VALUES (1,'abc'), (2,'xxx'), (3, 'mm')

3 rows affected

INSERT INTO Table2 VALUES (1,'abcd'), (3, 'nnnn')

2 rows affected

select Table1.Id, 
case when Table2.Name is null
    then Table1.Name
    else Table2.Name 
end as Name
from Table1
left join Table2 on Table2.Id = Table1.Id

fiddle