Skip to content
Advertisement

Get data from both table, where data isn’t common for this tables

I have 2 tables

TABLE jt1

name
---
A
B
C

and TABLE jt2

name
---
B
C
D

I need get names from both tables, which is not common for both tables, that is result must be

result
------
A
D

This is my query, but may be there is better solution for this?

    SELECT jt1.name  AS name  FROM jt1
    LEFT JOIN jt2
    ON jt1.name = jt2.name
    WHERE jt2.name IS NULL
    
    UNION
    
    SELECT jt2.name  AS name  FROM jt2
    LEFT JOIN jt1
    ON jt2.name = jt1.name
    WHERE jt1.name IS NULL

Advertisement

Answer

SELECT    COALESCE(jt1.name,  jt2.name) AS zname
FROM      jt1
FULL JOIN jt2 ON jt1.name = jt2.name
WHERE     jt2.name IS NULL OR jt1.name IS NULL
  ;

BTW: the naive solution could probably be faster:

SELECT name
FROM a (WHERE NOT EXISTS SELECT 1
        FROM b WHERE b.name = a.name)
UNION ALL
SELECT name 
FROM b (WHERE NOT EXISTS SELECT 1
        FROM a WHERE a.name = b.name)
   ;

BTW: I purposely use UNION ALL here, because I know that the two legs cannot have any overlap, and the removal of duplicates can be omitted.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement