MySQL SQL - 是否有相反但等效的 UNION 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12627746/
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 - Is there an opposite but equivalent UNION function?
提问by Tonya S
Is there an opposite but equivalent UNION function for mysql?
mysql 是否有相反但等效的 UNION 函数？
I know it's possible to do this with a longer query but I'm curious on whether or not there's a function equivalent.
我知道可以用更长的查询来做到这一点，但我很好奇是否有等价的函数。
I have 2 select statements for 2 different tables
我有 2 个选择语句用于 2 个不同的表
select state, state_name, id, idname, phone, mobile, home from table1
select state, state_name, id, idname, phone, mobile, home from table2
And I need a query that pulls only from table1 and ONLY if idname, phone, mobile, home from table1 doesn't match with table2
我需要一个查询，该查询仅从 table1 中提取，并且仅当 table1 中的 idname、phone、mobile、home 与 table2 不匹配时
For example: Table1 has
例如：表1有
AK | Alaska | 1 | row6 | 453 | 567 | 123
but Table 2 has:
但表 2 有：
AK | Alaska | 1 | row6 | 453 | 567 | 123
AK | Alaska | 1 | row6 | NULL | 567 | 123
AK | Alaska | 1 | tttttt | 453 | 567 | 123
The query will display
查询将显示
AK | Alaska | 1 | row6 | NULL | 567 | 123
AK | Alaska | 1 | tttttt | 453 | 567 | 123
will NOT display
不会显示
AK | Alaska | 1 | row6 | 453 | 567 | 123
回答by Martin Smith
In standard SQL you can use the ANSI SQL EXCEPToperator which is an exact analogue of UNION
在标准 SQL 中，您可以使用 ANSI SQLEXCEPT运算符，它与UNION
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
There is also an INTERSECTset operatorthat would show rows that both sources have in common.
还有一个INTERSECT集合运算符可以显示两个源共有的行。
Unfortunately neither of these are supported in current versions of MySQL so you need to use one of these other 3 more verbose ways of achieving an anti semi join.
不幸的是，当前版本的 MySQL 都不支持这两种方法，因此您需要使用其他 3 种更详细的方法之一来实现反半连接。
