SQL INTERSECT集合交运算

INTERSECT是结构化查询语言中的一个运算符，它会合并两个SELECT语句的行，并返回仅包含第一个SELECT语句与第二个SELECT语句相同的行的结果集。

简单来说，该运算符显示了两个SELECT语句中的共同行。

该运算符在MySQL数据库中不起作用。

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表中的Faculty中：

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;

以下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查询将Faculties的记录插入New_Faculty_Info表中：

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 New_Faculty_Info; 

以下查询显示了两个表中教师的共同记录：

SELECT * FROM Old_Faculty_InfoINTERSECT SELECT * FROM New_Faculty_Info;

输出：

INTERSECT操作符与WHERE子句

SQL WHERE子句也可以与INTERSECT操作符一起使用，用于从一个或两个表中过滤记录。

INTERSECT与WHERE子句的语法

SELECT Column_Name_1, Column_Name_2 ...., Column_Name_N FROM Table_Name_1 [WHERE condition]  
INTERSECT 
SELECT Column_Name1, Column_Name_2 ...., Column_Name_N FROM Table_Name_2 [WHERE condition];

INTERSECT和WHERE子句的示例

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

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

输出：

用ORDER BY子句的INTERSECT运算符

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

以下块显示了带有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; 

以下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表的数据。

SELECT * FROM New_Employee_Info; 

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

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