SQL query to find distinct values in two tables?
In proper RDBMS:
SELECT
T1.Code, T2.Code
FROM
(SELECT DISTINCT Code FROM Table1) T1
FULL OUTER JOIN
(SELECT DISTINCT Code FROM Table2) T2
ON T1.Code = T2.Code
In MySQL... the UNION removes duplicates
SELECT
T1.Code, T2.Code
FROM
Table1 T1
LEFT OUTER JOIN
Table2 T2 ON T1.Code = T2.Code
UNION
SELECT
T1.Code, T2.Code
FROM
Table1 T1
RIGHT OUTER JOIN
Table2 T2 ON T1.Code = T2.Code
In Standard SQL, using relational operators and avoiding nulls:
SELECT Code AS col_1, Code AS col_2
FROM Table_1
INTERSECT
SELECT Code AS col_1, Code AS col_2
FROM Table_2
UNION
SELECT Code AS col_1, 'missing' AS col_2
FROM Table_1
EXCEPT
SELECT Code AS col_1, 'missing' AS col_2
FROM Table_2
UNION
SELECT 'missing' AS col_1, Code AS col_2
FROM Table_2
EXCEPT
SELECT 'missing' AS col_1, Code AS col_2
FROM Table_1;
Again in Standard SQL, this time using constructs that MySQL actually supports:
SELECT Code AS col_1, Code AS col_2
FROM Table_1
WHERE EXISTS (
SELECT *
FROM Table_2
WHERE Table_2.Code = Table_1.Code
)
UNION
SELECT Code AS col_1, 'missing' AS col_2
FROM Table_1
WHERE NOT EXISTS (
SELECT *
FROM Table_2
WHERE Table_2.Code = Table_1.Code
)
UNION
SELECT 'missing' AS col_1, Code AS col_2
FROM Table_2
WHERE NOT EXISTS (
SELECT *
FROM Table_1
WHERE Table_1.Code = Table_2.Code
);
What you're looking for is a full outer join
:
select a.code as code_1,b.code as code_2
from(
select code
from table1
group by 1
)a
full outer join(
select code
from table2
group by 1
)b
using(code)
order by 1;