SQL UNION ALL and UNION ALL syntax and examples

Source: Internet
Author: User

SQL UNION ALL
UNION ALL the purpose of this instruction is to merge the results of two SQL statements together. The union all and the Union differ in that the Union all lists each qualifying data, regardless of whether the data value is duplicated or not. The syntax for union ALL is as follows: [SQL statement 1]
UNION ALL
[SQL Statement 2] We use the same example as the previous page to show the difference between Union and union. Also assume that we have the following two forms,

Store_information form Store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles $300 jan-08-1999
Boston $700 jan-08-1999

Internet Sales Form Date Sales
jan-07-1999 $
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750

And we need to find out where the store turnover and network turnover date. To achieve this, we use the following SQL statement: select date from Store_information
UNION ALL

SQL UNION operator

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

Note that the SELECT statement within the Union must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

SQL Union syntax
Select COLUMN_NAME (s) from table_name1
Union
Select COLUMN_NAME (s) from table_name2

Note: By default, the union operator chooses a different value, that is, the union is gone heavy. 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 names in the union result set are always equal to the column names in the first SELECT statement in the Union.

The purpose of the Union directive is to combine the results of two SQL statements. From this point of view, the Union is somewhat similar to the join, since these two instructions can be retrieved from multiple tables. The union simply joins the two results together to show that it is not connected to two tables ... the syntax of the Union is as follows:

[SQL statement 1]
Union
[SQL Statement 2] Suppose we have the following two tables,

Store_information form Store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles $300 jan-08-1999
Boston $700 jan-08-1999

Internet Sales Form Date Sales
jan-07-1999 $
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750

And we're going to find all the sales days. To achieve this, we use the following SQL statement: select date from Store_information
Union
Select date from internet_sales results:

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 of the SQL statements (or both), we will get exactly the same result.

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.