Union only concatenates two results and displays them together. It does not join two tables ............ The syntax of UNION is as follows: [SQL statement 1]
UNION
[SQL statement 2] assume that we have two tables: Store_Information table store_name Sales Date.
The code is as follows: |
Copy code |
Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999 Internet Sales table Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 January 12-1999 $750
|
We need to find out all the days with a turnover. To achieve this goal, we use the following SQL statement: SELECT Date FROM
The code is as follows: |
Copy code |
Store_Information UNION SELECT Date FROM Internet_Sales result: Date Jan-05-1999 Jan-07-1999 Jan-08-1999 Jan-10-1999 Jan-11-1999 Jan-12-1999 |
It is worth noting that if we use "select distinct Date" in any SQL statement (or both sentences), we will get the same result.
SQL UNION ALL syntax
The code is as follows: |
Copy code |
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 the same as the column name in the first SELECT statement in the UNION.
UNION ALL
The union all command is almost equivalent to the UNION command, but the union all command lists ALL values.
SQL Statement 1
UNION ALL
SQL Statement 2
Use the union all command
Instance:
List all employees in China and the United States:
The code is as follows: |
Copy code |
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 |
If there are no duplicate rows, we recommend that you use union all instead of sorting.
1. The order by clause must be written in the last result set, and its sorting rules will change the sorting result after the operation. Valid for Union, Union All, Intersect, and Minus.
2. Union can merge result sets with different field names but the same data type;
3. If different result sets of field names are Union, the Order by clause of this field will be invalid.
4. Union: perform Union operations on the two result sets, excluding duplicate rows, and sort the default rules at the same time;
5. Union All: perform Union operations on the two result sets, including duplicate rows without sorting;
6. Intersect: intersection of two result sets, excluding duplicate rows, and sorting by default rules;
7. Minus performs the difference operation on the two result sets, excluding duplicate rows, and sorts the default rules at the same time.
8. You can specify the Order by clause in the last result set to change the sorting method.