SQL SQL查询在两个表中查找不同的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7731406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query to find distinct values in two tables?
提问by tomthorgal
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 Codevalues and get a return like this:
我想找到不同的Code值并得到这样的回报：
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?
我不知道如何编写一个可以返回上述结果的 SQL 查询。任何人对这样或类似的查询有任何经验吗？
回答by gbn
In proper RDBMS:
在适当的 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
在 MySQL 中... UNION 删除重复项
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
回答by tomthorgal
What you're looking for is a full outer join:
你要找的是一个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;
回答by onedaywhen
In Standard SQL, using relational operators and avoiding nulls:
在标准 SQL 中，使用关系运算符并避免空值：
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:
再次在标准 SQL 中，这次使用 MySQL 实际支持的结构：
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
);
回答by Paul Sasik
This actually looks like a UNION of two outer joins. Try this:
这实际上看起来像是两个外部连接的 UNION。尝试这个：
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.
UNION 操作只会保留不同的值。
回答by ctupper
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
