Comparison and usage of UNION and union all in SQL

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.