Mysql 简明教程
MySQL - MINUS Operator
MySQL MINUS Operator
MySQL MINUS 运算符是其中一个集合运算符，用来提取独一无二的记录，这些记录来自一个表却不存在于另一个表中。换句话说，MINUS 运算符会比较两个表，然后返回第一张表中独一无二且不存在于第二张表中的行。
The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table.
我们设想以下图表来理解 MINUS 运算：
Let’s consider the following diagram to understand the MINUS operation:
正如我们在上述图表中所看到的，MINUS 运算符返回了一个包含 1 和 3 值的表作为输出，因为它们是 table1 中而不存在于 table2 中的无重复的值。
As we can see in the above diagram, the MINUS operator returned the table containing 1 and 3 values as output because they are the distinct values in table1 which do not exist in table2.
Syntax
下面是 MINUS 运算符的基本语法 -
Following is the basic syntax of the MINUS operator −
SELECT column_lists FROM table_name WHERE (condition)
MINUS
SELECT column_lists FROM table_name WHERE (condition);MySQL does not support the MINUS operator ，很遗憾。但是，我们可以使用 MySQL JOIN 子句来代替 MINUS 运算符。
Unfortunately MySQL does not support the MINUS operator. However we can use the MySQL JOIN clause instead of MINUS operator.
以下是 JOIN 子句的语法，用来执行 MINUS 运算 -
Following is the syntax for JOIN clause to perform MINUS operation −
SELECT column_list FROM table_name1
LEFT JOIN table_name2 ON join_predecate
WHERE table_name2.column_name IS NULL;Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
Firstly, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);此处，我们使用 INSERT 语句向 CUSTOMERS 表中插入一些记录 -
Here, we are inserting some records into the CUSTOMERS table using the INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);该表将被创建为：
The table will be created as −
现在让我们创建一个名为 ORDERS 的另一个表，使用以下查询 -
Now, let us create another table named ORDERS using the below query −
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);此处，我们使用 INSERT INTO 语句向 ORDERS 表中插入一些记录 -
Here, we are inserting some records into the ORDERS table using the INSERT INTO statement −
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);该表创建如下 −
The table is created as follows −
由于 MySQL 不支持 MINUS 运算符，我们可以使用 JOIN 来执行此操作 -
As MySQL does not support the MINUS operator instead we can use the JOIN to perform this operation −
以下查询通过连接两个表，选择所有没有订单的顾客 -
The following query selects all the customers who do not have any orders by joining two tables −
SELECT ID, NAME, AGE FROM CUSTOMERS
LEFT JOIN ORDERS USING (ID)
WHERE ORDERS.ID IS NULL;Minus Operator Using Client Program
除了使用 SQL 查询在 MySQL 表中执行 Minus 运算符外，还可以使用客户端程序对表执行其他运算。
In addition to executing the Minus Operator in MySQL table using an SQL query, we can also perform the another operation on a table using a client program.