Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
Description
Parentisizing UNION expressions is sometimes to change the order of evaluation and to add clarity is not only useful, but is also allowed in SQL:2011 as far as I can see. In addition, this gets more important when MariaDB adds support for INTERSECT in 10.3.
MariaDB Example:
$ mysql -u visma -pvisma appl_owner
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 116 |
Server version: 10.3.0-MariaDB MariaDB Server |
|
|
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. |
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. |
|
|
MariaDB [appl_owner]> create table t1(c1 int); |
Query OK, 0 rows affected (0.02 sec) |
|
|
MariaDB [appl_owner]> insert into t1 values(1),(2); |
Query OK, 2 rows affected (0.00 sec) |
Records: 2 Duplicates: 0 Warnings: 0 |
|
|
MariaDB [appl_owner]> select c1 from t1 union all select c1 from t1;
|
+------+
|
| c1 |
|
+------+
|
| 1 | |
| 2 | |
| 1 | |
| 2 | |
+------+
|
4 rows in set (0.00 sec) |
|
|
MariaDB [appl_owner]> (select c1 from t1 union all select c1 from t1);
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'union all select c1 from t1)' at line 1 |
With Oracle, this works, and here is also an example where this makes a difference:
$ sqlplus anders/XXXXXXXX
|
|
|
SQL*Plus: Release 11.2.0.2.0 Production on Thu Aug 31 14:36:01 2017 |
|
|
Copyright (c) 1982, 2011, Oracle. All rights reserved. |
|
|
|
|
Connected to:
|
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production |
|
|
SQL> create table t1(c1 int) tablespace users; |
|
|
Table created.
|
|
|
SQL> insert into t1 values(1); |
|
|
1 row created. |
|
|
SQL> insert into t1 values(2); |
|
|
1 row created. |
|
|
SQL> select c1 from t1 union select c1 from t1 union all select c1 from t1;
|
|
|
C1
|
----------
|
1 |
2 |
1 |
2 |
|
|
SQL> select c1 from t1 union (select c1 from t1 union all select c1 from t1);
|
|
|
C1
|
----------
|
1 |
2 |
|
|
SQL> (select c1 from t1 union all select c1 from t1);
|
|
|
C1
|
----------
|
1 |
2 |
1 |
2 |