Sql 简明教程

SQL - INTERSECT

在数学集合论中，两个集合的交集是这两个集合共有的一组值。

In mathematical set theory, the intersection of two sets is a collection of values that are common to both sets.

在实际场景中，数据库中将有大量包含信息的数据表。用户可能觉得很难从各种数据表中收集常见的信息。因此我们使用INTERSECT运算符来实现该目标。它有助于从各种数据表中检索常见数据。

In real-time scenarios, there will be a huge number of tables in a database that contains information. The user may find it challenging to gather common information from various tables. So we use the INTERSECT operator to accomplish that. It helps to retrieve the common data from various tables.

The SQL INTERSECT Operator

SQL中的 INTERSECT 运算符用于检索在两个或两个以上数据表的记录集中相同/常见的数据。

The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two or more tables.

让我们考虑下面的数据表作为示例，以更好地理解−

Let us consider the below tables as an example to get a better understanding −

intersect

如果对上面描述的两个数据表使用 INTERSECT 运算符执行交集运算，它会返回公共记录 Dev 和 Aarohi 。

If we perform the intersection operation on both tables described above using the INTERSECT operator, it returns the common records which are Dev and Aarohi.

Syntax

以下是Microsoft SQL Server中INTERSECT运算符的SQL语法−

Following is the SQL syntax of INTERSECT operator in Microsoft SQL Server −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

Example

首先，让我们使用以下查询创建一个名为 STUDENTS 的数据表−

First of all, let us create a table named STUDENTS using the following query −

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

使用以下查询将一些值插入到表中：

Let’s insert some values into the table using the following query −

INSERT INTO STUDENTS VALUES
(1, 'Naina', 'Maths', 24, 'Cricket'),
(2, 'Varun', 'Physics', 26, 'Football'),
(3, 'Dev', 'Maths', 23, 'Cricket'),
(4, 'Priya', 'Physics', 25, 'Cricket'),
(5, 'Aditya', 'Chemistry', 21, 'Cricket'),
(6, 'Kalyan', 'Maths', 30, 'Football');

所生成的表如下所示：

The table produced is as shown below −

ID

NAME

SUBJECT

AGE

HOBBY

1

Naina

Mathematics

24

Cricket

2

Varun

Physics

26

Football

3

Dev

Mathematics

23

Cricket

4

Priya

Physics

25

Cricket

5

Adithya

Chemistry

21

Cricket

6

Kalyan

Mathematics

30

Football

现在，让我们使用以下查询创建一个名为 STUDENTS_HOBBY 的另一个表：

Now, let us create another table named STUDENTS_HOBBY using the following query −

CREATE TABLE STUDENTS_HOBBY(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

一旦创建了该表，让我们使用以下查询向该表中插入一些值：

Once the table is created, let us insert some values to the table using the query below −

INSERT INTO STUDENTS_HOBBY VALUES
(1, 'Vijay', 'Cricket', 18),
(2, 'Varun', 'Football', 26),
(3, 'Surya', 'Cricket', 19),
(4, 'Karthik', 'Cricket', 25),
(5, 'Sunny', 'Football', 26),
(6, 'Dev', 'Cricket', 23);

创建的表如下所示：

The table created is as follows −

ID

NAME

HOBBY

AGE

1

Vijay

Cricket

18

2

Varun

Football

26

3

Surya

Cricket

19

4

Karthik

Cricket

25

5

Sunny

Football

26

6

Dev

Cricket

23

现在，我们使用以下查询从这两个表中检索通用记录：

Now, we are retrieving the common records from both the tables using the following query −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS;

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

NAME

AGE

HOBBY

Dev

23

Cricket

Varun

26

Football

INTERSECT with BETWEEN Operator

我们可以在 SQL 中将 INTERSECT 运算符与 BETWEEN 运算符一起使用，以查找落在指定范围内的记录。

We can use the INTERSECT operator with the BETWEEN operator in SQL to find records that fall within a specified range.

Example

现在，让我们从“STUDENTS”和“STUDENTS_HOBBY”这两个表中检索年龄在 25 到 30 岁之间的学生的姓名、年龄和爱好，仅返回指定年龄范围内的通用行：

Now, let us retrieve the name, age, and hobby of students aged between 25 and 30 from both the 'STUDENTS' and 'STUDENTS_HOBBY' tables, returning only the common rows within the specified age range −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30;

Output

以上查询的输出如下所示：

The output for the above query is produced as given below −

NAME

AGE

HOBBY

Varun

26

Football

INTERSECT with IN Operator

我们还可以在 SQL 中将 INTERSECT 运算符与 IN 运算符一起使用，以查找存在于指定值列表中的通用记录。IN 运算符用于根据指定值列表过滤结果集。

We can also use the INTERSECT operator with the IN operator in SQL to find the common records that exists in the specified list of values. The IN operator is used to filter a result set based on a list of specified values.

Example

以下 SQL 查询返回在“STUDENTS”和“STUDENTS_HOBBY”这两个表中将“Cricket”作为其爱好的学生的姓名、年龄和爱好：

The following SQL query returns the name, age, and hobby of students who have 'Cricket' as their hobby in both 'STUDENTS' and 'STUDENTS_HOBBY' tables −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE HOBBY IN('Cricket')
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN('Cricket');

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

NAME

AGE

HOBBY

Dev

23

Cricket

INTERSECT with LIKE Operator

LIKE 运算符用于对字符串执行模式匹配。INTERSECT 运算符也可以与 SQL 中的 LIKE 运算符一起使用，以查找与指定模式匹配的通用行。

The LIKE operator is used to perform pattern matching on a string. The INTERSECT operator can also be used with the LIKE operator in SQL to find the common rows that matches with the specified pattern.

Example

以下查询使用 LIKE 运算符中的通配符“%”从这两个表的通用名称中检索以“V”开头的名称：

The query below retrieves the names that start with 'V' using the wildcard '%' in the LIKE operator from the common names of both tables −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE NAME LIKE 'v%'
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE 'v%';

Output

以上查询的输出如下所示：

The output for the above query is produced as given below −

NAME

AGE

HOBBY

Varun

26

Football