Database set Query

Source: Internet
Author: User

Database set Query

Database collection operations mainly include three aspects:

1. Union merge rows

Union is used to merge two query results and deduplicate the same row while merging.

For example:


If we need to display all the union sets without duplicates, we can use union all, as shown below:


We only need to use union to connect two select-from-where statement blocks.

Union has the following notes:

1) union is an Interchangeable operation. The result of A union B is the same as that of B union;

2) theoretically, the sequence of select statement blocks in union does not affect the running speed, but may be affected in practical applications. We try to put the query of small tables in front of union, because the optimizer merges intermediate results and removes duplicate rows in different ways. Of course, the impact of different DBMS may be different;

3) intersect has a higher priority than union and except T. Intersect is used to find the same row, and interval t is used to find different rows. This is also related to different DBMS;

4) when you can use a combined select-from-where statement block, try not to use union;

5) when union and union all are used in combination, brackets should be used to clarify the merging order, because this involves the query results to be merged;

6) even if the order by statement is not used, the union results may be sorted, and union all does not need to be sorted because it does not have to be duplicated. Sorting takes a lot of extra time, so we try not to use union if we can use union all;

2. intersect searches for the same row

The intersect operation is to take the same row of the two query results.

MySQL does not support intersect. We can use the exists predicate to implement the same function.

3. Couldn't find different rows

A branch t B is the search result for A but not B.

MySQL does not support ipvt. We can use predicates such as not exists and not in to implement the same function.

Related Article

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.