Mysql --- union usage

Union is used to merge the result sets of two SQL statements.

Syntax: SQL1 UNION SQL2

An existing price list

Requirements: <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> Vc3Ryb25nPsfzs/weight + weight/Ix/weight + CjxwPtTZx/weight + CjxwPtTZwPvTw3VuaW9uus + weight + CjxwPjxpbWcgc3JjPQ = "http://www.2cto.com/uploadfile/Collfiles/20140618/2014061808570348.jpg" alt = "\">

When can union be used?

A: As long as the two result sets have the same number of columns, they can be used even if the field types are different.

The first result set has two columns, and the second result set has one column. After running, a message indicating different columns is displayed.

The number of columns is the same, but the field types are different. For example, goods_name is a character type and goods_id is an integer, but it can also be union.It is worth noting that the names of fields after union are subject to the first SQL

Sorting of union

It can be seen that the inner layer sorting does not work. Now try sorting in the outer layer.

It can be seen that outer sorting has taken effect.

So is it useless to sort the inner layer? In fact, from another perspective, we can sort the inner layer first, and the outer layer is sorted again. Obviously, the inner layer sorting seems redundant. Therefore, MYSQL optimizes the SQL statements and does not allow the inner layer sorting to take effect. To enable inner sorting, the result of inner sorting must affect the final result. For example, addLimit

In this way, the inner sorting takes effect.

What if two duplicate rows appear?

Create two more tables,

Table

Table B

Table A and table B have two identical records: 'wang wu' and 'xiao gao'

Use union to check the result.

We can see that there is only one record between 'wang 5' and 'xiaogao '. Therefore, if the other record is overwritten, you can useUnion all

, 'Wang 5' and 'xiaogao 'are not covered