MySQL不支持INTERSECT和MINUS及其替代方法
2019/6/30 18:43:10
本文主要是介绍MySQL不支持INTERSECT和MINUS及其替代方法,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Doing INTERSECT and MINUS in MySQL
Doing an INTERSECT
An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. So
SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b
can simply be rewritten to
SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)
Performing a MINUS
To transform the statement
SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b
into something that MySQL can process, we can utilize subqueries (available from MySQL 4.1 onward). The easy-to-understand transformation is:
SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN
(SELECT member_id, name FROM table2);
Of course, to any long-time MySQL user, this is immediately obvious as the classical use-left-join-to-find-what-isn't-in-the-other-table:
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
Doing an INTERSECT
An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. So
复制代码 代码如下:
SELECT member_id, name FROM a
INTERSECT
SELECT member_id, name FROM b
can simply be rewritten to
复制代码 代码如下:
SELECT a.member_id, a.name
FROM a INNER JOIN b
USING (member_id, name)
Performing a MINUS
To transform the statement
复制代码 代码如下:
SELECT member_id, name FROM a
MINUS
SELECT member_id, name FROM b
into something that MySQL can process, we can utilize subqueries (available from MySQL 4.1 onward). The easy-to-understand transformation is:
复制代码 代码如下:
SELECT DISTINCT member_id, name
FROM a
WHERE (member_id, name) NOT IN
(SELECT member_id, name FROM table2);
Of course, to any long-time MySQL user, this is immediately obvious as the classical use-left-join-to-find-what-isn't-in-the-other-table:
复制代码 代码如下:
SELECT DISTINCT a.member_id, a.name
FROM a LEFT JOIN b USING (member_id, name)
WHERE b.member_id IS NULL
这篇关于MySQL不支持INTERSECT和MINUS及其替代方法的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-09-21MySQL集群部署资料:新手入门教程
- 2024-09-21MySQL集群资料:初学者入门指南
- 2024-09-21部署MySQL集群资料:新手入门教程
- 2024-09-20MySQL集群部署教程:新手入门指南
- 2024-09-20MySQL集群教程:初学者必备指南
- 2024-09-20部署MySQL集群项目实战:新手入门教程
- 2024-09-20如何部署MySQL集群:简单教程
- 2024-09-20MySQL集群部署:新手入门指南
- 2024-09-20部署MySQL集群学习:入门指南
- 2024-09-20部署MySQL集群入门教程