Sql 简明教程

SQL - UNION vs UNION ALL

UNION 和 UNION ALL 运算符只是代数集合运算符的 SQL 实现。它们都用于从多个表中检索行，并将其作为单个表返回。这两个运算符之间的区别在于 UNION 仅返回不同的行，而 UNION ALL 返回表中存在的所有行。

UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables.

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

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

  1. The tables to be combined must have the same number of columns with the same datatype.

  2. The number of rows need not be the same.

满足这些条件后，UNION 或 UNION ALL 运算符会将来自多个表的行作为结果表返回。

Once these criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table.

What is UNION?

UNION 是 SQL 中的一种运算符/子句，其工作方式类似于关系代数中的并运算符。它仅合并来自与并兼容的多个表的信息。

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It just combines the information from multiple tables that are union compatible.

只有来自表的不同行被添加到结果表中，因为 UNION 会自动消除所有重复记录。

Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records.

Syntax

以下是 UNION 运算符在 SQL 中的语法 -

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

让我们首先使用具有相同数据类型相同数量的列创建两个表 COURSES_PICKED 和 EXTRA_COURSES_PICKED。

Let us first create two tables COURSES_PICKED and EXTRA_COURSES_PICKED with the same number of columns having the same data types.

使用以下查询创建表 COURSES_PICKED：

Create table COURSES_PICKED using the following query −

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

使用下面给出的查询向 COURSES_PICKED 表中插入值：

Insert values into the COURSES_PICKED table with the help of the query given below −

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

表将显示如下 -

The table will be displayed as shown below −

STUDENT_ID

STUDENT_NAME

COURSE_NAME

1

JOHN

ENGLISH

2

ROBERT

COMPUTER SCIENCE

3

SASHA

COMMUNICATIONS

4

JULIAN

MATHEMATICS

现在，让我们使用以下查询创建另一个表 EXTRA_COURSES_PICKED -

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

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

表将按如下所示创建 -

The table will be created as shown below −

STUDENT_ID

STUDENT_NAME

COURSES_PICKED

1

JOHN

PHYSICAL EDUCATION

2

ROBERT

GYM

3

SASHA

FILM

4

JULIAN

MATHEMATICS

现在，让我们使用以下 UNION 查询合并这两个表 -

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

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

执行 UNION 操作后获得的结果表如下 -

The resultant table obtained after performing the UNION operation is as follows −

STUDENT_ID

STUDENT_NAME

COURSE_NAME

1

JOHN

ENGLISH

2

ROBERT

COMPUTER SCIENCE

3

SASHA

COMMUNICATIONS

4

JULIAN

MATHEMATICS

1

JOHN

PHYSICAL EDUCATION

2

ROBERT

GYM

3

SASHA

FILM

What is UNION ALL?

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

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

假设有两张表，其中一张表包含一个玩家在国际赛事中参赛的比赛数量，另一张包含一个玩家在国内联赛中出场的比赛数量。

Suppose there are two tables, one of which contains the number of games a player competed in internationally and the other contains the number of games a player played nationally.

union vs unionall

正如我们在上面的表中所看到的，科利在国际比赛中的出场次数为 234 场，在国内赛场中的出场次数也为 234 场。尽管这些列中的数据相同，但它们都是独立的比赛。需要将这两行都包含在显示一个玩家的总比赛场次的最终表中。因此，在这些情况下，我们使用 UNION ALL 运算符。

As we can see in the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases.

union vs unionall1

Syntax

以下是 SQL 中 UNION ALL 运算符的语法：

Following is the syntax of UNION ALL operator in SQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

在以下示例中，我们针对上面给出的相同的示例表“COURSES_PICKED”和“EXTRA_COURSES_PICKED”执行 UNION ALL 操作，使用如下给出的查询：

In the following example, let us perform UNION ALL operation on the same sample tables given above: "COURSES_PICKED" and "EXTRA_COURSES_PICKED", using the given query below −

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

最终表如下所示：

The resultant table is displayed as follows −

STUDENT_ID

STUDENT_NAME

COURSE_NAME

1

JOHN

ENGLISH

2

ROBERT

COMPUTER SCIENCE

3

SASHA

COMMUNICATIONS

4

JULIAN

MATHEMATICS

1

JOHN

PHYSICAL EDUCATION

2

ROBERT

GYM

3

SASHA

FILM

4

JULIAN

MATHEMATICS