Mysql 简明教程

MySQL - Intersect Operator

在数学集合论中，两个集合的交集是一个只包含同时属于两个集合的那些元素的集合。换句话说，两个集合的交集是一个包含存在于两个集合中的元素的集合。

In mathematical set theory, the intersection of two sets is a set that contains only those elements that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets.

如果使用 INTERSECT 运算符对两个集合执行交集运算，它将显示两个表中的公共行。此运算符将从最终结果集中删除重复的行。

If we perform the intersection operation on both sets using the INTERSECT operator, it displays the common rows from both tables. This operator removes the duplicate rows from the final result set.

MySQL INTERSECT Operator

在 MySQL 中，INTERSECT 运算符用于返回两个 SELECT（表）语句结果集之间的相同/公共记录。

In MySQL, the INTERSECT operator is used to return the records that are identical/common between the result sets of two SELECT (tables) statements.

但是，INTERSECT 运算符仅适用于两个 SELECT 语句具有相同数量的具有相同数据类型和名称的列时。

However, the INTERSECT operator works only if both the SELECT statements have an equal number of columns with same data types and names.

Syntax

下面是 MySQL 中 INTERSECT 运算符的语法 −

Following is the syntax of INTERSECT operator in MySQL −

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,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

这里，我们使用 INSERT 语句向表中插入一些值。

Here, we are inserting some values into the table using the INSERT statement.

INSERT INTO STUDENTS 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 is created as follows −

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

Now, let us create another table with name ASSOCIATES using the following query −

CREATE TABLE ASSOCIATES(
   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)
);

这里，我们使用 INSERT 语句向表中插入一些值 −

Here, we are inserting some values into the table using the INSERT statement −

INSERT INTO ASSOCIATES 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 is created as follows −

现在，我们使用以下查询返回两个表中的公共记录 −

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

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

Output

得到的输出如下 -

The output is obtained as follows −

INTERSECT with BETWEEN Operator

MySQL INTERSECT 运算符可以与 BETWEEN 运算符一起使用，以查找存在于指定范围内的行。

The MySQL INTERSECT operator can be used with the BETWEEN operator to find the rows that exist within the specified range.

Example

在以下查询中，我们正在检索两个表中都通用的记录。此外；我们正在检索年龄在 25 到 30 岁之间的记录 −

In the following query, we are retrieving the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 −

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

Output

执行给定的程序后，输出如下所示：

On executing the given program, the output is displayed as follows −

INTERSECT with IN Operator

在 MySQL 中，我们可以将 INTERSECT 运算符与 IN 运算符一起使用，以查找具有指定值的公共行。IN 运算符用于根据指定值列表过滤结果集。

In MySQL, we can use the INTERSECT operator with IN operator to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values.

Example

在以下查询中，我们尝试返回两个表中的公共记录。此外；我们使用 th IN 运算符检索爱好为“板球”的记录。

In the following query, we are trying to return the common records from both tables. In addition; we are using th IN operator to retrieve the records whose hobby is “Cricket”.

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

Output

以上程序的输出如下所示：

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

INTERSECT with LIKE Operator

LIKE 运算符用于对字符串值执行模式匹配。

The LIKE operator is used to perform pattern matching on a string value.

我们可以在 MySQL 中将 LIKE 运算符与 INTERSECT 运算符一起使用，以查找与指定模式匹配的公共行。

We can use the LIKE operator with the INTERSECT operator in MySQL to find the common rows that match the specified pattern.

Example

在以下查询中，我们使用通配符 '%' 与 LIKE 运算符一起，从两个表的公共名称中提取带有 'v' 的名称。

In the following query, we are using the wildcard '%' with the LIKE operator to fetch the names with 'v' from the common names of both tables.

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

Output

让我们编译并运行程序，以生成以下结果 −

Let us compile and run the program, to produce the following result −

Intersect Operator Using Client Program

除了在 MySQL 服务器中执行相交运算符之外，我们还可以在表上使用客户端程序执行 INTERSECT 运算符。

In addition to executing the Intersect Operator in MySQL server, we can also execute the INTERSECT operator on a table using a client program.

Syntax

以下是 MySQL 表中的 Intersect 运算符在各种编程语言中的语法 −

Following are the syntaxes of the Intersect Operator in MySQL table in various programming languages −

Example

以下是该操作在各种编程语言中的实现 −

Following are the implementations of this operation in various programming languages −