The usage and efficiency of the SQL Union and Union all, and the difference from join

Source: Internet
Author: User

One: The purpose of the Union directive is to combine the results of two SQL statements.

Let's say we have the following two tables:
Store_information table: Store_name Sales Date


Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles jan-08-1999
Boston $700 jan-08-1999


Internet Sales table: Date Sales


jan-07-1999
jan-10-1999 $535
jan-11-1999 $320
jan-12-1999 $750

If we want to find out all the days that have turnover (sales). 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

The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned.

The purpose of the UNION all directive is to merge the results of the two SQL statements together. The Union all and union differ in that union all lists each qualifying material, regardless of whether the data value is duplicated or not.

If we only use union ALL in the above example, the result will be:

Date
jan-05-1999
jan-07-1999
jan-08-1999
jan-08-1999
jan-07-1999
jan-10-1999
jan-11-1999
jan-12-1999

Summary: Union combines data with the same results and removes all duplicate data.

Two: Join and Union differences

A join is a record set in which two tables are connected and the same part of the same condition is created.

The Union is the resulting two recordset (the field is the same) and together, becomes a new recordset.

The usage and efficiency of the SQL Union and Union all, and the difference from join

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.