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引發的慢查詢
