SQL query to find distinct values in two tables?

In proper RDBMS:

SELECT   T1.Code, T2.CodeFROM   (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.CodeFROM   Table1 T1   LEFT OUTER JOIN   Table2 T2 ON T1.Code = T2.CodeUNIONSELECT   T1.Code, T2.CodeFROM   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_1INTERSECTSELECT Code AS col_1, Code AS col_2  FROM Table_2UNIONSELECT Code AS col_1, 'missing' AS col_2  FROM Table_1EXCEPTSELECT Code AS col_1, 'missing' AS col_2  FROM Table_2UNIONSELECT 'missing' AS col_1, Code AS col_2  FROM Table_2EXCEPTSELECT '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              )  UNIONSELECT 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                  )  UNIONSELECT '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_2from(  select code  from table1  group by 1)afull outer join(  select code  from table2   group by 1)busing(code)order by 1;