Database set Query

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.