The Ultimate Guide to High Availability with MariaDB

Precedence control over table operation (UNION, EXCEPT, INTERSECT)

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.4.0

Beginning from 10.4 one can use brackets to control table operations.

The syntax is following:

Syntax

(  expression )
[ORDER BY [column [, column ...]]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Where expression is SELECT or Table Value Constructor possibly connected with one or several UNION/EXCEPT/INTERSECT operations

Contents

  1. Syntax
    1. Description
    2. Example

Description

The expression in the brackets executed first (there could be optimisations but result should be the same as if it was executed first).

ORDER BY and LIMIT clauses works normally but could be optimized out.

Example

create table t1 (a int);
insert into t1 values (1),(2),(3);
(select * from t1 union values (10)) intersect values (1),(3),(10),(11);
+------+
| a    |
+------+
|    1 |
|    3 |
|   10 |
+------+
((select * from t1 union values (10)) intersect values (1),(3),(10),(11)) order by 1 desc;
+------+
| a    |
+------+
|   10 |
|    3 |
|    1 |
+------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.