First day of SQL Study -- SQL union and Union all operators (1)

SQL Union operator

The Union operator is used to merge the result sets of two or more select statements.

Note:

1. The SELECT statement within Union must have the same number of columns. Columns must also have similar data types. In addition, the columns in each select statement must be in the same order.
2. The column name in the Union result set is always the same as the column name in the first select statement in the Union.

SQL Union syntax (The result set has no repeated values.):

SELECT s FROM table1UNIONSELECT s FROM table2

SQL Union all syntax (the result set has repeated values ):

SELECT s FROM table1UNION ALLSELECT s FROM table2

Example Table:

Table 1 (employees_china ):

　　　　E_id e_name

01 Zhang, Hua

02 Wang, Wei

03 Carter, Thomas

04 Yang, Ming

Table 2 (employees_usa ):

　　E_id e_name

01 Adams, John

02 bush, George

03 Carter, Thomas

04 gates, Bill

Instance:

AboutUNION：
Select e_name from employees_china
　　UNION
Select e_name from employees_usa
The result is as follows (removing duplicate data ):

E_name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

 

 

 

 

 

 

 

 

 

 

AboutUNION ALL：
　　SELECT E_Name FROM Employees_China
　　

　　UNION ALL


　　SELECT E_Name FROM Employees_USA
The result is as follows (repeated data is retained ):

E_name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill