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
- > SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>> SELECT FROM TABLE D OL>
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).
- Ctfhub error injection
- 12-web security — error injection based on SQL Server — and, convert, cast
- New fields in hive table and modification of field comments
- MySQL advanced — Explanation of ref field in explain information
- Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
- DB2 table annotation pull
- Floor() error injection
- MySql ERROR CODE：1215 Cannot add the foreign key constraint
- MySQL failed to add foreign key: SQL 1452 cannot add or update a child row:a foreign key constraint fails
- About MySQL error: subquery returns more than 1 row
- R note for Bioinfo: the column for the select call is undefined
- Group by operator of hive execution plan
- Mysql reports an error Operand should contain 1 column(s)
- JS prompt cannot read property ‘style’ of undefined
- mysql error 1093
- The value of adding two fields of MySQL
- Ora-00947: not enough values
- MySQL error: column ‘ID’ in field list is ambiguous
- Perfectly solve the 1366 error of saving Chinese in MySQL
- 【error】postgresql relation does not exist