SQL - 是否存在相反但等效的UNION函数？

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语句

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

例如：Table1有

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

2 个解决方案

#1

In standard SQL you can use the ANSI SQL EXCEPT operator which is an exact analogue of UNION

在标准SQL中，您可以使用ANSI SQL EXCEPT运算符，它与UNION完全类似

SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1

There is also an INTERSECT set operator that 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种更冗长的方式来实现反半连接。

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL

NOT IN与NOT EXISTS vs. LEFT JOIN / IS NULL

#2

If you're using Oracle, you'll use the MINUS clause.

如果您使用的是Oracle，则将使用MINUS子句。

statement 1
MINUS
statement 2

要么

statement 1
WHERE NOT EXISTS (statement 2)

#1