SQL减法（Minus）

Minus是结构化查询语言（SQL）中的一种运算符，它与两个SELECT查询一起使用。

此运算符只返回第一个表的唯一记录，而不是两个表的相同记录。

结构化查询语言中Minus运算符的语法：

SELECT Column_Name_1, Column_Name_2, …., Column_Name_N FROM Table_Name_1

减号

SELECT Column_Name1, Column_Name_2, …., Column_Name_N FROM Table_Name_2;

计算机领域中使用的HTML格式英文文本不变，翻译如下：

每个与“Minus” SQL运算符连接的SELECT语句的数据类型和字段数量必须相同。

SQL中Minus运算符的示例

为了理解在Structured Query Language中使用Minus运算符的示例，我们需要创建两个不同的表Old_Worker_Info和New_Worker_Info，并在两个表中插入Workers的记录。

以下查询在Industry数据库中创建了 Old_Worker_Info 表：

CREATE TABLE Old_Worker_Info
(
Worker_ID INT NOT NULL PRIMARY KEY,  
Worker_First_Name VARCHAR (100),  
Worker_Last_Name VARCHAR (100),  
Worker_Dept_Id INT NOT NULL,
Worker_Joining_Date Varchar (80),
Worker_City Varchar (80),
Worker_Salary INT 
);

以下的INSERT查询将记录插入到Old_Worker_Info表中的老员工：

INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000);
INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 );
INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000);
INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000);
INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000);
INSERT INTO Old_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);

以下查询显示Old_Worker_Info表的数据。

SELECT * FROM Old_Worker_Info;

以下查询在 行业 数据库中创建了 New_Worker_Info 表：

CREATE TABLE New_Worker_Info
(
Worker_ID INT NOT NULL PRIMARY KEY,  
Worker_First_Name VARCHAR (100),  
Worker_Last_Name VARCHAR (100),  
Worker_Dept_Id INT NOT NULL,
Worker_Joining_Date Varchar (80),
Worker_City Varchar (80),
Worker_Salary INT 
);

以下INSERT查询将新员工的记录插入New_Worker_Info表中：

INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1010, Ankush, Roy, 4004, 2018-10-02, Delhi, 25000);
INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000);
INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1009, Raj, Singhania, 4005, 2021-05-10, Noida, 40000);
INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000);
INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary) VALUES (1008, Avinabh, Chetya, 4002, 2018-11-11, Banglore, 22000);
INSERT INTO New_Worker_Info (Worker_ID, Worker_First_Name, Worker_Last_Name Worker_Dept_Id, Worker_Joining_Date, Worker_City, Worker_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);

步骤4：查看插入的数据

以下查询显示了New_Worker_Info表的数据。

SELECT * FROM New_Worker_Info;

以下查询使用了上面的表格中的Minus运算符:

SELECT * FROM Old_Worker_Info Minus SELECT * FROM New_Worker_Info;

输出：

减法运算符与WHERE子句

WHERE子句还可以与减法运算符一起使用，用于过滤第一个表的记录。

带WHERE子句的减法运算符语法

SELECT Column_Name_1, Column_Name_2 …., Column_NameN FROM Table_Name_1 [WHERE condition]

减法

SELECT Column_Name1, Column_Name_2 …., Column_Name_N FROM Table_Name_2 [WHERE condition];

Minus带有WHERE子句的示例

下面的查询显示了上述表中工人工资大于等于35000的记录：

SELECT * FROM Old_Worker_Info WHERE Worker_Salary >= 35000 Minus SELECT * FROM New_Worker_Info;

输出：