|
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
|
|