SQL INTERSECT

2025年2月2日 | 阅读6分钟

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 教师的记录插入到这两个表中。

以下 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 表中

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 表的记录

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	德里	20000
1002	Bulbul	Roy	4002	2019-12-31	德里	38000
1004	Saurabh	Roy	4001	2020-10-10	孟买	45000
1005	Shivani	Singhania	4001	2019-07-15	加尔各答	42000
1006	Avinash	Sharma	4002	2019-11-11	德里	28000
1007	Shyam	Besas	4003	2021-06-21	勒克瑙	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 表中

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

步骤 4：查看插入的数据

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

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1010	Ankush	Roy	4004	2018-10-02	德里	25000
1001	Arush	Sharma	4001	2020-01-02	德里	20000
1009	Raj	Singhania	4005	2021-05-10	Noida	40000
1005	Shivani	Singhania	4001	2019-07-15	加尔各答	42000
1008	Avinabh	Chetya	4002	2018-11-11	Banglore	22000
1007	Shyam	Besas	4003	2021-06-21	勒克瑙	35000

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

输出

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	德里	20000
1005	Shivani	Singhania	4001	2019-07-15	加尔各答	42000
1007	Shyam	Besas	4003	2021-06-21	勒克瑙	35000

带有 WHERE 子句的 INTERSECT 运算符

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

带有 WHERE 子句的 INTERSECT 语法

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

带有 WHERE 子句的 INTERSECT 示例

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

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	加尔各答	42000
1007	Shyam	Besas	4003	2021-06-21	勒克瑙	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 表的数据。

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

下一主题如何在 SQL 中使用 UNION

SQL INTERSECT

SQL 中 INTERSECT 运算符的示例

步骤 4：查看插入的数据

带有 WHERE 子句的 INTERSECT 运算符

带有 WHERE 子句的 INTERSECT 示例

带有 ORDER BY 子句的 INTERSECT 运算符

示例

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL 教程

SQL 数据库

SQL 表

SQL Select

SQL 子句

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL 键

Interview

SQL 多选题

SQL 注入

区别

PL/SQL 教程

其他

SQL 字符串函数

测验

SQL INTERSECT

SQL 中 INTERSECT 运算符的示例

步骤 4：查看插入的数据

带有 WHERE 子句的 INTERSECT 运算符

带有 WHERE 子句的 INTERSECT 示例

带有 ORDER BY 子句的 INTERSECT 运算符

示例

相关帖子

SQL 中的 LEFT 函数

本地临时表和全局临时表之间的区别

更改 SQL 中列的数据类型

SQL GROUP BY 子句

SQL 中的 ACOS 函数

如何在 SQL 中使用 ORDER BY

SQL 中的 Savepoint

SQL 中的 CHAR 函数

SQL 中的不同星号模式

SQL 中的 LIMIT 函数

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器