Shulou(Shulou.com)06/03 Report--

SQL UNION and UNION ALL operators

SQL Full Join

SQL Select Into

SQL UNION operator

The UNION operator is used to merge the result sets of two or more SELECT statements.

Note that SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

SQL UNION syntax

SELECT column_name (s) FROM table_name1

UNION

SELECT column_name (s) FROM table_name2

Note: by default, the UNION operator selects a different value. If duplicate values are allowed, use UNION ALL.

SQL UNION ALL syntax

SELECT column_name (s) FROM table_name1

UNION ALL

SELECT column_name (s) FROM table_name2

In addition, the column name in the UNION result set is always equal to the column name in the first SELECT statement in UNION.

The original table used in the following example:

Employees_China:

E_ID E_Name

01 Zhang, Hua

02 Wang, Wei

03 Carter, Thomas

04 Yang, Ming

Employees_USA:

E_ID E_Name

01 Adams, John

02 Bush, George

03 Carter, Thomas

04 Gates, Bill

Use the UNION command

Example

List all different employee names in China and the United States:

SELECT E_Name FROM Employees_China

UNION

SELECT E_Name FROM Employees_USA

Result

E_Name

Zhang, Hua

Wang, Wei

Carter, Thomas

Yang, Ming

Adams, John

Bush, George

Gates, Bill

Note: this order does not list all employees in China and the United States. In the above example, we have two employees with the same name, and only one of them has been listed. The UNION command only selects different values.

UNION ALL

The UNION ALL command and the UNION command are almost equivalent, but the UNION ALL command lists all the values.

SQL Statement 1

UNION ALL

SQL Statement 2

Use the UNION ALL command

Example:

List all employees in China and the United States:

SELECT E_Name FROM Employees_China

UNION ALL

SELECT E_Name FROM Employees_USA

Result

E_Name

Zhang, Hua

Wang, Wei

Carter, Thomas

Yang, Ming

Adams, John

Bush, George

Carter, Thomas

Gates, Bill

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.