MySQL---the use of Union

Union is simple enough to combine the result set of two SQL

Syntax: SQL1 UNION SQL2

Present a price list

Requirements: Find the price below 2000 and the price is higher than 3000 goods, can not use OR and not Between......and

idea: We can find a product below 2000 to get the result set 1

Find more than 3000 of the product to get the result set 2

Re-use Union to merge result set 1 and result set 2

When will I be allowed to use union?

A: You can use the same number of columns as two result sets, even if the field types are different.

The first result set has 2 columns, the second result set has 1 columns, and prompts to prompt for a different number of columns after the run.

The number of columns is the same, but the field types are different. such as Goods_name is a character type and goods_id is cosmetic, but can also be union. It is important to note that the name of the field after the Union is the first SQL

About the sort of union

The visible inner sort does not work, so now try to sort the outer layers

Visible outer sort has played a role

That is not the inner layer of the sort is useless, in fact, a different angle to think about the inner layer of the first sort, the outer order, the obvious inner layer sort seems redundant, so MySQL optimized the SQL statement, do not let the inner layer sort function. In order for the inner sort to work, the results of the inner order must be affected. such as: Plus limit

So that the inner layer sort would work.

What happens if you have repeated rows two times?

Create a further 2 tables,

Table A

Table B

Table A and Table B have two identical records ' Harry ', ' small high '

Now use union to look at the results

You can see that ' Harry ' and ' Little high ' have only one record, so another record is overwritten, so you can use union ALL if you want to not be overwritten.

, ' Harry ' and ' little high ' are not covered