Sql 简明教程

SQL - UNION Operator

The SQL UNION Operator

SQL UNION 运算符通过消除重复行(如果有)来组合来自多张表的数据。

要在多张表上使用 UNION 运算符,所有这些表必须具有并集兼容性。并且仅当它们符合以下条件时,才认为它们具有并集兼容性 −

  1. 选择相同数量的具有相同数据类型的列。

  2. 这些列还必需按相同顺序排列。

  3. 它们不需要有相同数目的行。

满足这些条件后,UNION 运算符会将来自多个表的行作为结果表返回,该表不包含任何这些表中的重复值。

Syntax

UNION 运算符的基本语法如下 -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition];

此处,给定的条件可以是基于您的要求的任何给定表达式。

UNION on a Single Field

如果我们想使用 UNION 将单个字段上两个或更多 SELECT 语句的结果集组合在一起,我们只需在每个查询的 SELECT 语句中包含该字段。UNION 运算符会自动删除最终结果集中任何重复的值。

Example

假设我们已使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表,如下所示 −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

以下查询使用 INSERT 语句将值插入此表:

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

CUSTOMERS 表如下 -

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

现在,使用 CREATE TABLE 语句创建第二个表 ORDERS,如下所示 -

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,
   PRIMARY KEY (OID)
);

以下查询使用 INSERT 语句将值插入此表:

INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000),
(100, '2009-10-08 00:00:00', 3, 1500),
(101, '2009-11-20 00:00:00', 2, 1560),
(103, '2008-05-20 00:00:00', 4, 2060);

ORDERS 表如下 -

OID

DATE

CUSTOMER_ID

AMOUNT

102

2009-10-08 00:00:00

3

3000.00

100

2009-10-08 00:00:00

3

1500.00

101

2009-11-20 00:00:00

2

1560.00

103

2008-05-20 00:00:00

4

2060.00

使用以下查询,让我们组合 CUSTOMERS 和 ORDERS 表中的 SALARY 和 AMOUNT 列(因为这些列具有相似的的数据类型) -

SELECT SALARY FROM CUSTOMERS UNION SELECT AMOUNT FROM ORDERS;

Output

以上查询的输出如下 −

SALARY

2000.00

1500.00

6500.00

8500.00

4500.00

10000.00

3000.00

1560.00

2060.00

UNION on Multiple Fields

当我们在多个字段上使用 UNION 时,每个 SELECT 语句中的字段的数量和顺序都必须匹配。此外,每个 SELECT 语句中字段的数据类型必须兼容,以便 UNION 正确工作。如果数据类型不兼容,您可能需要使用转换函数(如 CAST 或 CONVERT)来确保数据类型匹配。

Example

由于 CUSTOMERS 和 ORDERS 表单独不是联合兼容的,因此让我们首先使用左联接和右联接将这两个表联接到一个更大的表中。检索到的联接表将具有相同数量的、相同数据类型的列,从而成为联合兼容的。现在,使用如下所示的 UNION 查询组合这些表 -

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

这将产生以下结果 -

ID

NAME

AMOUNT

DATE

1

Ramesh

NULL

NULL

2

Khilan

1560

2009-11-20 00:00:00

3

Kaushik

3000

2009-10-08 00:00:00

3

Kaushik

1500

2009-10-08 00:00:00

4

Chaitali

2060

2008-05-20 00:00:00

5

Hardik

NULL

NULL

6

Komal

NULL

NULL

7

Muffy

NULL

NULL

UNION with WHERE Clause

我们可以在 UNION 运算符中使用 WHERE 子句来在组合之前过滤每个 SELECT 语句的结果。

Syntax

下面是使用 UNION 运算符与 WHERE 子句一起的语法 -

SELECT column1, column2, column3
FROM table1
WHERE column1 = 'value1'
UNION
SELECT column1, column2, column3
FROM table2
WHERE column1 = 'value2';

Example

在以下查询中,我们检索 id 分别大于 5 和 2 的客户的 id,分别来自“CUSTOMERS”和“ORDERS”表 -

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2;

Output

以下是产生的结果:

ID

SALARY

6

4500.00

7

10000.00

3

3000.00

3

1500.00

4

2060.00

UNION with ORDER BY Clause

当我们在 ORDER BY 子句中使用 UNION 时,它会组合所有 SELECT 语句的有序结果集并生成单个的有序结果集。

Example

在这里,我们检索 id 分别大于 5 和 2 的客户,分别来自“CUSTOMERS”和“ORDERS”表,从其工资中从小到大进行排序 -

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2
ORDER BY SALARY;

Output

以下是以上查询的输出:

ID

SALARY

3

1500.00

4

2060.00

3

3000.00

6

4500.00

7

10000.00

UNION with Aliases

我们可以在 UNION 运算符的 SELECT 语句中使用别名,为表或列赋予一个临时名称,当使用多个表或具有相似名称的列时,这可能非常有用。

与别名一起使用 UNION 时,需要注意的是列别名是由第一个 SELECT 语句决定的。因此,如果您想为不同 SELECT 语句中的同一列使用不同的别名,则需要在所有 SELECT 语句中使用列别名,以确保最终结果集中列名称的一致性。

Syntax

下面是用别名使用 Union 的语法 -

SELECT column1 AS alias1, column2 AS alias2
FROM table1
UNION
SELECT column3 AS alias1, column4 AS alias2
FROM table2;

Example

以下查询同时从两个表中检索所有 ID,并指示每个 ID 是属于客户还是客户下的订单−

SELECT ID, 'customer' AS type FROM CUSTOMERS
UNION
SELECT OID, 'order' AS type FROM ORDERS;

Output

下面是生成的输出−

ID

type

1

customer

2

customer

3

customer

4

customer

5

customer

6

customer

7

customer

100

order

101

order

102

order

103

order

还有两个与 UNION 运算符类似的运算符。

  1. SQL INTERSECT Operator − 用于合并两个 SELECT 语句,但只返回与第二个 SELECT 语句中的行完全相同的第一个 SELECT 语句的行。

  2. SQL EXCEPT Operator − 合并两个 SELECT 语句,并返回第一个 SELECT 语句中未返回的行的行。