Mysql 简明教程

MySQL - UNION vs UNION ALL

MySQL 中的 UNION 和 UNION ALL 运算符用于在多个表中检索行，并将它们返回为单个表。

UNION and UNION ALL operators in MySQL are used to retrieve the rows from multiple tables and return them as one single table.

但是，对于这些运算符在这些表上工作，它们需要遵循以下给定的条件 -

However, for these operators to work on these tables, they need to follow the conditions given below −

The tables to be combined must have the same number of columns with the same datatype.
The number of rows need not be the same.

MySQL UNION Operator

MySQL 中的 UNION 运算符类似于关系代数中的并集运算符。该运算符将来自多个表的兼容并集的信息合并起来。但是，只有来自表的不同行会被添加到结果表中，因为 UNION 会自动消除所有重复记录。

UNION operator in MySQL works similar to the union operator in relational algebra. This operator combines information from multiple tables that are union compatible. However, only distinct rows from the tables are added to the result table, as UNION automatically eliminates all the duplicate records.

Syntax

下面是 MySQL 中 UNION 运算符的语法：

Following is the syntax of UNION operator in MySQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

MySQL UNION ALL Operator

UNION ALL 也是 MySQL 中的一个运算符/子句，用于将多个表合并为一个表。但是，该运算符也会保留结果表中的重复行。

UNION ALL is also an operator/clause in MySQL, used to combine multiple tables into one. However, this operator also preserves the duplicate rows in the resultant tables.

Syntax

下面是 MySQL 中 UNION ALL 运算符的语法：

Following is the syntax of UNION ALL operator in MySQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

MySQL UNION VS MySQL UNION ALL

Example

首先，我们需要创建两个表，列数相同，数据类型也相同。以下查询创建一个名为 “COURSES_PICKED” 的表：

First of all we need to create two tables namely with the same number of columns having the same data types. Following query creates a table named "COURSES_PICKED" −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

以下查询向 COURSES_PICKED 表中插入值：

Following query inserts values into the COURSES_PICKED table −

INSERT INTO COURSES_PICKED VALUES
(1, 'JOHN', 'ENGLISH'),
(2, 'ROBERT', 'COMPUTER SCIENCE'),
(3, 'SASHA', 'COMMUNICATIONS'),
(4, 'JULIAN', 'MATHEMATICS');

COURSES_PICKED 表中的内容如下：

The contents of the COURSES_PICKED table will be as follows −

现在，让我们创建另一个名为 EXTRA_COURSES_PICKED 的表，如下所示：

Now, lets create another table named EXTRA_COURSES_PICKED as shown below −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

以下是在 EXTRA_COURSES_PICKED 表中插入值的查询 −

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, 'JOHN', 'PHYSICAL EDUCATION'),
(2, 'ROBERT', 'GYM'),
(3, 'SASHA', 'FILM'),
(4, 'JULIAN', 'MATHEMATICS');

EXTRA_COURSES_PICKED 表中的内容如下：

The contents of the EXTRA_COURSES_PICKED table will be as follows −

Combining tables using UNION Operator

现在，让我们使用 UNION 查询合并这两个表，如下所示：

Now, let us combine both these tables using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

以下是所获得的输出 −

Following is the output obtained −

在此，“Julian” 和 “Shasha” 挑选的额外课程和课程相同，因此 COURSES_PICKED 和 EXTRA_COURSES_PICKED 中的最后两条记录是相同的。当我们使用 UNION 运算符合并两个表时，它会排除相同记录，只返回不同的记录。

In here, the extra courses and courses picked by "Julian" and "Shasha" are same therefore, the last two records in the COURSES_PICKED and EXTRA_COURSES_PICKED are identical. When we combine both the tables using the UNION operator it excludes the identical records and returns only distinct records.

Combining tables using UNION ALL Operator

与 UNION 运算符不同，如果我们使用 UNION ALL 运算符合并上述创建的表，它会重新运行这两个表中的所有记录：

Unlike the UNION operator if we combine the above created tables using the UNION ALL operator it reruns all the records in both tables −

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

最终表如下所示：

The resultant table is displayed as follows −