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.