Combine two tables, exclude same records

Question

I have two tables:

T1 
A  
B  
C  
D

T2  
A  
B  
E  
F  
G  

Now I want to have query that will combine those two tables but exclude same records. The output table should be like:

T1T2 
C  
D  
E  
F  
G

How to do that?

Answer 1

Looks like you need FULL OUTER JOIN and exclude common part. You can simulate it with:

SELECT T1.col_name
FROM T1 
LEFT JOIN T2
  ON T1.col_name = T2.col_name
WHERE T2.col_name IS NULL
UNION
SELECT T2.col_name
FROM T2 
LEFT JOIN T1
  ON T1.col_name = T2.col_name
WHERE T1.col_name IS NULL;

SqlFiddleDemo

╔══════════╗
║ col_name ║
╠══════════╣
║ C        ║
║ D        ║
║ E        ║
║ F        ║
║ G        ║
╚══════════╝

---

More info: Visual Representation of SQL Joins

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

Unfortunately MySQL does not support FULL OUTER JOIN so I used union of 2 LEFT JOIN.

^{All images from http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins}

Addendum

But what if I have two different tables with different columns, but both of them have one same column? The used SELECT statements have a different number of columns

You could easily expand it with additional columns.

SELECT 'T1' AS tab_name, T1.col_name, T1.col1, NULL AS col2
FROM  T1
LEFT JOIN  T2
  ON T1.col_name=  T2.col_name
WHERE T2.col_name IS NULL
UNION
SELECT 'T2' AS tab_name, T2.col_name, NULL, T2.col2
FROM  T2
LEFT JOIN  T1
  ON T1.col_name=  T2.col_name
WHERE T1.col_name IS NULL;

LiveDemo

Output:

╔══════════╦══════════╦══════╦═════════════════════╗
║ tab_name ║ col_name ║ col1 ║        col2         ║
╠══════════╬══════════╬══════╬═════════════════════╣
║ T1       ║ C        ║    3 ║                     ║
║ T1       ║ D        ║    4 ║                     ║
║ T2       ║ E        ║      ║ 2016-01-03 00:00:00 ║
║ T2       ║ F        ║      ║ 2016-01-02 00:00:00 ║
║ T2       ║ G        ║      ║ 2016-01-01 00:00:00 ║
╚══════════╩══════════╩══════╩═════════════════════╝

Answer 2

I see two possible solutions.

Using UNION ALL together with an outer select with GROUP BY x HAVING COUNT(x) = 1:

SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT a FROM t2) as t12 GROUP BY a HAVING COUNT(a) = 1

Using UNION to combine two SELECTs filtered by subqueries:

(SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2)) UNION (SELECT a FROM t2 WHERE a NOT IN (SELECT a FROM t1))

Not sure which one is most performant!

See it in action here.

Answer 3

Try to intersect the two tables. you can see the differences.

SELECT T1.col_name
FROM (
SELECT T1.col_name FROM T1
UNION ALL
SELECT T2.col_name FROM T2
) T1
GROUP BY col_name
HAVING count(*) = 1
ORDER BY col_name;

Answer 4

Assuming you have no duplicates in either table, one approach uses union all and aggregation:

select col1, col2, . . .
from (select t1.* from table1 t1
      union all
      select t2.* from table2 t2
     ) tt
group by col1, col2, . . .
having count(*) = 1;

Actually, you can relax the condition on not having duplicates, but then you need to specify what to do with them.