Enumeration type and collection type of MySQL
CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE

create table consumer(
    id int,
    name char(16),
    sex enum('male','female','other'),
    level enum('vip1','vip2','vip3'),
    hobbies set('play','music','read','run')
)

Enumeration here means that you can select only one from here, and set means that you can select more than one from set.
Input insert statement

insert into consumer values(1,'egon','male','vip2','music,read')

And you might be wondering, what happens if I put multiple entries in the corresponding enum?

insert into consumer values(2,'eg','male','vip1,vip2','music,read')

ah

The SQL UNION operator
The UNION operator is used to combine the result sets of two or more SELECT statements.
Note that the SELECT statement within the UNION must have the same number of columns. Columns must also have similar data types. Also, the order of the columns in each SELECT statement must be the same.
The UNION SQL grammar

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: By default, the UNION operator picks a different value. If duplicate values are allowed, use UNION ALL.
SQL UNION ALL syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

Also, the column name in the UNION result set is always equal to the column name in the first SELECT statement in the UNION.
Error reporting and resolution
When using the UNION operator, the following error can easily be reported:
Parse error: org. Apache. Hadoop. Hive. Ql. Parse. ParseException: line 5-0 always recognize input near “and” (” and “the UNION, ‘ ‘SELECT’ set in the operator
Simplify the code used as follows:

SELECT
    device_id
FROM
    tableA
UNION
(SELECT
    device_id
FROM
    tableB as a1
INNER JOIN tableC as a2
on a1.device_id = a2.device_id
)

This is mainly because the UNION operator can only join fields to fields, but not fields to tables or tables, even if the number of fields and their names and formats are the same.
Therefore, you can’t have parentheses around the UNION operator, because it’s easy for SQL to determine that this is a subquery/table and therefore to report an error when joining
The solution

> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D> SELECT FROM TABLE D
Matters needing attention
> Multiple UNION operators can be used in SQL statements. SELECT statements that use the UNION operator cannot contain their own ORDER BY or COMPUTE clauses. You can only use an ORDER BY or COMPUTE clause on the final combined result set (that is, after the last SELECT statement). The GROUP BY and HAVING clauses can be used in SELECT statements that use the UNION operator. By default, SQL Server 2005 evaluates statements containing the UNION operator from left to right. You can specify the order of evaluation using parentheses (I haven’t tested this thoroughly on any other platform, so avoid parentheses altogether, so if you need to specify the order of evaluation, you can simply write that part of the evaluation at the beginning of your SQL statement).