The Ultimate Guide to High Availability with MariaDB

Precedence Control in Table Operations

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

Beginning in MariaDB 10.4, you can control the ordering of execution on table operations using parentheses.

Syntax

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

Contents

  1. Syntax
  2. Description
  3. Example

Description

Using parentheses in your SQL allows you to control the order of execution for SELECT statements and Table Value Constructor, including UNION, EXCEPT, and INTERSECT operations. MariaDB executes the parenthetical expression before the rest of the statement. You can then use ORDER BY and LIMIT clauses the further organize the result-set.

Note: In practice, the Optimizer may rearrange the exact order in which MariaDB executes different parts of the statement. When it calculates the result-set, however, it returns values as though the parenthetical expression were executed first.

Example

CREATE TABLE test.t1 (num INT);

INSERT INTO test.t1 VALUES (1),(2),(3);

(SELECT * FROM test.t1 
 UNION 
 VALUES (10)) 
INTERSECT 
VALUES (1),(3),(10),(11);
+------+
| num  |
+------+
|    1 |
|    3 |
|   10 |
+------+

((SELECT * FROM test.t1 
  UNION 
  VALUES (10)) 
 INTERSECT 
 VALUES (1),(3),(10),(11)) 
ORDER BY 1 DESC;
+------+
| num  |
+------+
|   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.