SQL Minus 运算符

一、SQL Minus 运算符 语法

Minus 是SQL语言中的一个运算符，用于两个 SELECT 查询。

Minus 运算符仅返回第一个表的唯一记录，而不返回两个表的公共记录。

Minus 运算符 语法：

SELECT Column_Name_1, Column_Name_2, …., Column_Name_N FROM Table_Name_1  
minus
SELECT Column_Name1, Column_Name_2, …., Column_Name_N FROM Table_Name_2;

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

二、SQL Minus 运算符 示例

要理解SQL语言中 Minus 运算符的示例，我们必须创建两个不同的表 Old_Worker_Info 和 New_Worker_Info，并在这两个表中插入 Workers 的记录。

以下查询在行业数据库中创建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 表中插入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;  

输出结果为：

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

以下查询在行业数据库中创建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);  

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

SELECT * FROM New_Worker_Info;  

输出结果为：

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

以下查询对上述表使用 Minus运算符：

SELECT * FROM Old_Worker_Info Minus SELECT * FROM New_Worker_Info;  

输出结果为：

Worker_Id	Worker_First_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
1002	Bulbul	Roy	4002	2019-12-31	Delhi	38000
1004	Saurabh	Roy	4001	2020-10-10	Mumbai	45000
1006	Avinash	Sharma	4002	2019-11-11	Delhi	28000

三、带有 WHERE 子句的 Minus运算符

WHERE 子句也可以与 Minus运算符一起用于过滤第一个表的记录。

带 WHERE 子句的 Minus语法

SELECT Column_Name_1, Column_Name_2 …., Column_NameN FROM Table_Name_1 [WHERE condition]
minus
SELECT Column_Name1, Column_Name_2 …., Column_Name_N FROM Table_Name_2 [WHERE condition];  

以下查询显示了上表中薪水大于等于 35000 的工人记录：

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

输出结果为：

Worker_Id	rst_Name	Worker_Last_Name	Worker_Dept_Id	Worker_Joining_Date	Worker_City	Worker_Salary
1002	Bulbul	Roy	4002	2019-12-31	Delhi	38000
1004	Saurabh	Roy	4001	2020-10-10	Mumbai	45000