I have 2 tables
TABLE jt1
x
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.