SQL query to find distinct values in two tables?

Table 开发者_运维知识库1              Table 2
Number | Code        Code | Description
1234     A           A      Something
1235     B           C      Something else
1246     C           D      Something other
1247     A
1248     B
1249     A

I would like to find the distinct Code values and get a return like this:

1  |  2
-------
A     A
B
C     C
      D

I can't figure out how to write a SQL query that would return me the above results. Anyone have any experience with a query like this or similar?

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;

This actually looks like a UNION of two outer joins. Try this:

SELECT t1.Code, t2.Code
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.Code

UNION

SELECT t1.Code, t2.Code
FROM Table1 AS t1
RIGHT JOIN Table2 AS t2 ON t1.Code

ORDER BY 1, 2

The UNION operation will only keep distinct values.

The trick would be to get the distinct values from both tables, something like this:

SELECT a.Code, b.code
FROM
( --Get the DISTICT Codes from all sets
    SELECT Distinct Code from Table1
    UNION SELECT Distinct Code from Table2
) x Left JOIN
Table1 a ON x.code = a.Code LEFT JOIN
Table2 b ON x.code = b.Code

SELECT  
   ct.ct_id, 
   ct.pd_id, 
   ct.ct_qty, 
   pd.product_name, 
   pd.price, 
   src.service_id, 
   src.service_name, 
   src.service_charge, 
   src.service_quantity
FROM 
   cart ct, 
   product pd, 
   service src 
WHERE ct_session_id = '$sid' 
LIMIT 1