SQL INTERSECT 相交

一、SQL INTERSECT 相交 介绍

INTERSECT 是SQL语言中的一个运算符，它组合了两个 SELECT 语句的行并仅返回第一个 SELECT 语句中的那些行，这些行与第二个 SELECT 语句的行相同。

简单来说，我们可以说这个运算符显示了两个 SELECT 语句中的公共行。

注意：INTERSECT 运算符在 MySQL 数据库中不起作用。

二、SQL INTERSECT 相交 语法

SELECT Column_Name_1, Column_Name_2, ...., Column_Name_N FROM Table_Name_1  
INTERSECT   
SELECT 1st_Column, Column_Name_2, ...., Column_Name_N FROM Table_Name_2  
INTERSECT ....... INTERSECT  
SELECT Column_Name1, Column_Name_2, ...., Column_Name_N FROM Table_Name_N ;  

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

三、SQL INTERSECT 相交 示例

要理解 INTERSECT 运算符的示例，我们必须在 SQL 中创建两个不同的表 Old_Faculty_Info 和 New_Faculty_Info，并在这两个表中插入 IIT Faculty 的记录。

以下 CREATE 语句在IIT_College数据库中创建Old_Faculty_Info表：

CREATE TABLE Old_Faculty_Info  
(  
Faculty_ID INT NOT NULL PRIMARY KEY,    
Faculty_First_Name VARCHAR (100),    
Faculty_Last_Name VARCHAR (100),    
Faculty_Dept_Id INT NOT NULL,  
Faculty_Joining_Date Varchar (80),  
Faculty_City Varchar (80),  
Faculty_Salary INT   
);  

以下 INSERT 语句 在 Old_Faculty_Info 表中插入 Faculties 的记录：

INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000);  
INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 );  
INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000);  
INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000);  
INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000);  
INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);  

以下 SQL 语句 用于查询 Old_Faculty_Info 表的记录

SELECT * FROM Old_Faculty_Info;  

表数据如下：

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_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

以下 CREATE 语句在IIT_College数据库中创建New_Faculty_Info表：

CREATE TABLE New_Faculty_Info  
(  
Faculty_ID INT NOT NULL PRIMARY KEY,    
Faculty_First_Name VARCHAR (100),    
Faculty_Last_Name VARCHAR (100),    
Faculty_Dept_Id INT NOT NULL,  
Faculty_Joining_Date Varchar (80),  
Faculty_City Varchar (80),  
Faculty_Salary INT   
);  

 以下 INSERT 语句在 New_Faculty_Info 表中插入 Faculties 的记录

INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1010, Ankush, Roy, 4004, 2018-10-02, Delhi, 25000);  
INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000);  
INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1009, Raj, Singhania, 4005, 2021-05-10, Noida, 40000);  
INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000);  
INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1008, Avinabh, Chetya, 4002, 2018-11-11, Banglore, 22000);  
INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);  

以下 SELECT 语句显示 New_Faculty_Info 表的数据。

SELECT * FROM Old_Faculty_InfoINTERSECT SELECT * FROM New_Faculty_Info; 

表数据如下：

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1001	Arush	Sharma	4001	2020-01-02	Delhi	20000
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

四、带 WHERE 子句的 INTERSECT 运算符 

SQL WHERE 子句也可以与 INTERSECT 运算符一起使用，以过滤来自一个或两个表的记录。

语法

SELECT Column_Name_1, Column_Name_2 ...., Column_Name_N FROM Table_Name_1 ORDER BY Column_Name;  
INTERSECT   
SELECT Column_Name1, Column_Name_2 ...., Column_Name_N FROM Table_Name_2 ORDER BY Column_Name;  

示例

以下查询显示了上述表中工资大于等于 35000 的 Faculties 记录

SELECT * FROM Old_Faculty_Info WHERE Faculty_Salary >= 35000 INTERSECT SELECT * FROM New_Faculty_Info Faculty_Salary >= 35000;  

输出结果为：

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

五、带有 ORDER BY 子句的 INTERSECT 运算符

SQL ORDER BY 子句也可以与 INTERSECT 运算符一起使用，以显示基于指定组的记录。

语法

SELECT Column_Name_1, Column_Name_2 ...., Column_Name_N FROM Table_Name_1 ORDER BY Column_Name;  
INTERSECT   
SELECT Column_Name1, Column_Name_2 ...., Column_Name_N FROM Table_Name_2 ORDER BY Column_Name;  

示例

要理解带有 Order By 子句的 Intersect 运算符的示例，我们必须在 SQL 中创建这两个表。

以下 CREATE 语句在 IT_Company 数据库中创建 Old_Employee_Info 表

CREATE TABLE Old_Employee_Info  
(  
Employee_ID INT NOT NULL,    
Employee_Name VARCHAR (100),  
Employee_Gender Varchar(20),  
Employee_Age INT NOT NULL DEFAULT 18,  
Employee_Salary INT NOT NULL  
);  

以下 INSERT 语句在 Old_Employee_Info 表中插入员工记录

INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1001, Arush, Male, 18, 35000);  
INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1002, Bulbul, Female, 18, 42000);  
INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1004, Saurabh, Male, 20, 45000);  
INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1005, Shivani, Female, 25, 28000);  
INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Address, Employee_Salary) VALUES (1006, Avinash, Male, 22, 38000);  
INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1007, Shyam, Male, 18, 20000);  

以下查询显示 Old_Employee_Info 表的数据

SELECT * FROM Old_Employee_Info;   

表数据如下：

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1001	Arush	Male	18	35000
1002	Bulbul	Female	18	42000
1004	Saurabh	Male	20	45000
1005	Shivani	Female	25	28000
1006	Avinash	Male	22	38000
1007	Shyam	Male	18	20000

以下 CREATE 语句在 IT_Company 数据库中创建 New_Employee_Info 表

CREATE TABLE New_Employee_Info  
(  
Employee_ID INT NOT NULL,    
Employee_Name VARCHAR (100),  
Employee_Gender Varchar(20),  
Employee_Age INT NOT NULL DEFAULT 18,  
Employee_Salary INT NOT NULL  
);  

 以下 INSERT 语句在 New_Employee_Info 表中插入员工记录

INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1007, Anuj, Male, 22 49000);  
INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1010, Saket, Male, 29, 69000);  
INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1005, Shivani, Female, 25, 28000);  
INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Address, Employee_Salary) VALUES (1006, Avinash, Male, 22, 38000);  
INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1009, Shyamo,   
Female, 18, 25000);  

以下查询显示了 New_Employee_Info 表的数据

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1007	Anuj	Male	22	49000
1010	Saket	Male	29	69000
1005	Shivani	Female	25	28000
1006	Avinash	Male	22	38000
1009	Female	Male	18	25000

以下查询按降序显示两个表中普通员工的记录

SELECT * FROM Old_Employee_Info ORDER BY Employee_Salary DESCINTERSECT SELECT * FROM New_Employee_Info ORDER BY Employee_Salary DESC;  

 结果为：

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1006	Avinash	Male	22	38000
1005	Shivani	Female	25	28000