合并两个表，排除相同的记录 [英] Combine two tables, exclude same records

问题描述

我有两张桌子:

T1一种乙CDT2一种乙乙FG

现在我想要查询将组合这两个表但排除相同的记录.输出表应该是这样的:

T1T2CD乙FG

怎么做?

解决方案

看起来您需要 FULL OUTER JOIN 并排除公共部分.你可以模拟它:

SELECT T1.col_name从 T1左连接 T2ON T1.col_name = T2.col_nameWHERE T2.col_name 为空联盟选择 T2.col_name从 T2左加入 T1ON T1.col_name = T2.col_nameWHERE T1.col_name 为空；

SELECT 从表_A AFULL OUTER JOIN Table_B BON A.Key = B.Key哪里 A.Key 为 NULL 或 B.Key 为 NULL

不幸的是 MySQL 不支持 FULL OUTER JOIN 所以我使用了 2 个 LEFT JOIN 的并集.

^{所有图片来自 http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins}

附录

<块引用>

但是如果我有两个不同的表有不同的列，但它们都有一个相同的列怎么办?使用的 SELECT 语句具有不同的列数

您可以使用其他列轻松扩展它.

SELECT 'T1' AS tab_name, T1.col_name, T1.col1, NULL AS col2从 T1左连接 T2ON T1.col_name= T2.col_nameWHERE T2.col_name 为空联盟SELECT 'T2' AS tab_name, T2.col_name, NULL, T2.col2从 T2左加入 T1ON T1.col_name= T2.col_nameWHERE T1.col_name 为空；

LiveDemo

输出:

╔=========╦=========╦======╦=============＝＝＝＝＝＝＝＝╗║ 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 ║╚=========╩=========╩=====╩===================╝

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?

解决方案

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 ║
╚══════════╩══════════╩══════╩═════════════════════╝

这篇关于合并两个表，排除相同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！