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中char与varchar的区别分析Mysql varchar大小长度问题介绍mysql VARCHAR的最大长度到底是多少mysql 开启慢查询 如何打开mysql的慢查询日志记录MySQL慢查询查找和调优测试MySQL前缀索引导致的慢查询分析总结深入mysql慢查询设置的详解MySQL的Replace into 与Insert into on duplicate key update真正的不同之处mysqlsla慢查询分析工具使用笔记MySQL中把varchar类型转为date类型方法详解MySQL数据库中把int转化varchar引发的慢查询
