How to use the MySQL---Union

Union is very easy to combine the result set of two SQL

Syntax: SQL1 UNION SQL2

Present a price list

Requirements: find goods with a price below 2000 and a price above 3000. Cannot use OR and not Between......and

idea: We can find a product of less than 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 do we agree to use Union?

A: Only the number of columns in the two result set can be used, even if the field type is not the same.

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

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbxlfbwfv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center ">

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 shaped, but the same can 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 level of the sort is not practical. In fact, a different angle to think about the inner layer of the first sort, the outer order, the obvious inner layer sort of superfluous. So MySQL optimized the SQL statement so that the inner sort does not work. To make the inner sort work, it is necessary to have the result of sorting the inner layers affect the result.

such as: Plus limit

Watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvbxlfbwfv/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/dissolve/70 /gravity/center ">

So that the inner layer sort would work.

What happens if there are repeated rows two times?

Create a further 2 tables,

Table A

Table B

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

Now use union to look at the results

Can see ' Harry ' and ' Little high ' just have a record, so there is a record is overwritten, to be able to use union all without being overwritten

What is seen, ' Harry ' and ' little high ' are not covered

How to use the MySQL---Union