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;
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查询将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;
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 | 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 |
以下查询显示了两个表中教师的共同记录:
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 |
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;
输出:
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运算符一起使用,以按指定的分组显示记录。
以下块显示了带有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表的数据。
SELECT * FROM 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 |