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;

Tags:

Sql

Distinct