MySQL不支持INTERSECT和MINUS及其替代方法
时间:2024-01-14 15:09:11
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
标签:MySQL,INTERSECT,MINUS
0
投稿
猜你喜欢
mysql8创建、删除用户以及授权、消权操作详解
2024-01-13 22:46:34
Python访问MySQL封装的常用类实例
2024-01-16 01:43:42
MySQL 原理与优化之原数据锁的应用
2024-01-27 05:43:51
搜索系统与导航系统的关系
2009-09-08 12:44:00
基于Python3制作一个带GUI界面的小说爬虫工具
2023-04-02 17:33:04
centos7 PHP环境搭建 GD库 等插件安装方法
2023-11-05 21:25:38
Mysql中TIMESTAMPDIFF函数的语法与练习案例
2024-01-28 18:43:44
python读取当前目录下的CSV文件数据
2022-04-06 16:50:35
实例解析js中try、catch、finally的执行规则
2024-04-22 13:25:49
opencv python 图片读取与显示图片窗口未响应问题的解决
2021-05-15 22:08:53
Python3.4解释器用法简单示例
2022-01-04 10:30:39
简介Django中内置的一些中间件
2023-03-16 19:20:27
js添加千分位的实现代码(超简单)
2023-08-25 07:43:29
Webform 内置对象 Session对象、Application全局对象,ViewState详细介绍
2023-06-30 02:14:07
Django admin禁用编辑链接和添加删除操作详解
2021-01-13 05:01:01
Windows+Anaconda3+PyTorch+PyCharm的安装教程图文详解
2021-07-10 03:34:55
tensorflow -gpu安装方法(不用自己装cuda,cdnn)
2021-01-26 09:06:46
PDO取Oracle lob大字段,当数据量太大无法取出的问题的解决办法
2024-01-15 00:47:43
MySQL中group_concat函数深入理解
2024-01-14 23:31:34
MySQL数据库卸载的完整步骤
2024-01-13 13:12:52