CODEWITHSUNDEEP
sqlpostgresql
GeoGyro
GeoGyro

Reputation: 559

union or join query without duplication

I try to write a query to select objects in two tables. In the first there is a list of id, in the second some of this id and a field with a specific value. I want to display all my id and the specific value, even if it's empty in the first table. Of course I don't want duplicate results.

I've tried join, union, under-select but I always have duplication on my id field. In SQL UNION is distinct by default, so it could do the trick but i don't know how to merge these 2 queries without duplication on id.

I make a diagram to be more visual: diagram

Some code, with duplication on id :

SELECT a.id, 'null' AS value
FROM tableA a
UNION
SELECT b.id, b.value
FROM tableB b;

Edit with a condition :

SELECT a.id, 'null' AS value
FROM tableA a
UNION
SELECT b.id, b.value
FROM tableB b
WHERE b.idtype = 1;

Upvotes: 0

Views: 611

Answers (4)

Thomas Phaneuf
Thomas Phaneuf

Reputation: 314

This is a pretty common situation. Here is the code:

SELECT  id      = A.id,
        value   = B.value, 
        source  = 
            CASE 
            WHEN B.id IS NULL THEN 'TableA'
            WHEN C.id IS NULL THEN 'TableB'
            ELSE 'Both'
            END
FROM    (
            SELECT  id 
            FROM    TableA
            UNION 
            SELECT  id
            FROM    TableB
        ) as A
        LEFT OUTER JOIN TableB as B
            ON A.id = B.id
        LEFT OUTER JOIN TableA as C
            ON A.id = C.id
ORDER BY id

The first part of the join (the UNION statement) establishes what I like to call "buckets", and the second part (TableB) fills them in where it can. What I like about this approach is that it reflects what you are conceptually trying to do.

An issue with the accepted answer is that if an Id exists in both TableA and TableB, the source will always come back as TableB - with no indication that it also exists in TableA. For example, if you were to insert a record into TableB with and id of 2 and a value of "Yellow", the source from that query would return "TableB" as the source. The query in this answer would return "Both". The point is, that this answer is easier to tweak.

Upvotes: 0

Artur Luiz Oliveira
Artur Luiz Oliveira

Reputation: 107

A GROUP BY approach to be able to work with grouped data in future:

select
    coalesce(a.id,b.id) uid,
    *
from b
FULL JOIN a
    ON a.id = b.id
GROUP BY uid;

Coalesce will use the first non-null value, that way you'll have all ID you need

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can use union all:

select id, value, 'TableB' as source
from b
union all
select id, NULL, 'TableA'
from a
where not exists (select 1 from b where b.id = a.id);

Alternatively, you can use full join:

select id, b.value,
       (case when b.id is not null then 'TableB' else 'TableA'
        end) as source
from a full join
     b
     using (id);

Upvotes: 1

GMB
GMB

Reputation: 222402

I think that you are looking for a left join:

select 
    ta.id,
    tb.value, 
    case when tb.id is null then 'Table A' else 'Table B' end source
from tablea ta
left join tableb tb on ta.id = tb.id

Upvotes: 1

Related Questions