CODEWITHSUNDEEP
sql
detrraxic
detrraxic

Reputation: 190

SQL query to append values not contained in second table

I have table A and table B with different number of columns but both containing a column with IDs. Table A contains more complete list of IDs and table B contains some of the IDs from the table A.

I would like to return resulting table B with original information plus appended IDs that are missing in B but contained in A. For these appended rows, other columns should be blank while column with IDs in B should just contain missing ID values.

Upvotes: 0

Views: 303

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you described left join:

select *
from b left join
     a
     using (id)

Upvotes: 0

jarlh
jarlh

Reputation: 44706

Simple solution UNION ALL, with NOT EXISTS:

select b.id, b.c1, ..., b.cn
from b
UNION ALL
select distinct a.id, null, ..., null       -- should be same number of columns as in the above select
from a
where not exists (select 1 from b where b.id = a.id)

Upvotes: 1

Related Questions