A2-02-22.DML-MySQL MINUS
转载自：http://www.mysqltutorial.org/mysql-minus/
MySQL MINUS（自己本次没有测试出预期的结果，估计与MySQL版本和SQL模式有关）
Summary: in this tutorial, you will learn how about SQL MINUS operator and how to simulate MySQL MINUS operator using join.
Introduction to SQL MINUS operator
MINUS is one of three set operations in the SQL standard that includes UNION, INTERSECT, and MINUS.
MINUS compares results of two queries and returns distinct rows from the first query that aren’t output by the second query.
The following illustrates the syntax of the MINUS operator:
|
1
2
3
|
SELECT column_list_1 FROM table_1
MINUS
SELECT columns_list_2 FROM table_2;
|
The basic rules for a query that uses MINUS operator are the following:
- The number and order of columns in both
column_list_1andcolumn_list_2must be the same. - The data types of the corresponding columns in both queries must be compatible.
Suppose we have two tables t1 and t2 with the following structure and data:
|
1
2
3
4
5
6
7
8
9
10
|
CREATE TABLE t1 (
id INT PRIMARY KEY
);
CREATE TABLE t2 (
id INT PRIMARY KEY
);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);
|
The following query returns distinct values from the query of the t1 table that are not found on the result of the query of the t2 table.
|
1
2
3
|
SELECT id FROM t1
MINUS
SELECT id FROM t2;
|
The following Venn diagram illustrates the MINUS operator:
Note that some database systems e.g., Microsoft SQL Server, PostgreSQL, etc., use the EXCEPT instead of MINUS, which have the same function.
MySQL MINUS operator
Unfortunately, MySQL does not support MINUS operator. However, you can use the MySQL join to simulate it.
To emulate the MINUS of two queries, you use the following syntax:
|
1
2
3
4
5
6
7
|
SELECT
column_list
FROM
table_1
LEFT JOIN table_2 ON join_predicate
WHERE
table_2.id IS NULL;
|
For example, the following query uses the LEFT JOIN clause to return the same result as the MINUSoperator:
|
1
2
3
4
5
6
7
8
|
SELECT
id
FROM
t1
LEFT JOIN
t2 USING (id)
WHERE
t2.id IS NULL;
|
In this tutorial, you have learned about the SQL MINUS operator and how to implement MySQL MINUS operator using LEFT JOIN clause.
