Mariadb Select

SELECT in MariaDB

Last updated: 2026-06-02

This skill covers the delta between standard SQL SELECT and MariaDB's. It assumes the agent already knows the standard form (SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …). Everything below documents MariaDB-specific syntax, locking semantics, file output, optimizer-hint variants, and the most common LLM traps. Consolidates SELECT, SELECT INTO OUTFILE, SELECT INTO DUMPFILE, SELECT … OFFSET … FETCH, and SELECT WITH ROLLUP into a single lookup.

Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.

What LLMs Often Miss

Optimizer Hints

Two distinct surfaces. Both exist; they're not interchangeable.

1. Hint-comment form (newer, recommended)

SELECT /*+ MAX_EXECUTION_TIME(5000) JOIN_ORDER(t2, t1) */
  t1.a, t2.b FROM t1 JOIN t2 ON t1.id = t2.id;

The hint-comment block must come immediately after SELECT, before any other modifiers. Multiple hints can share one comment. Available since 11.8 as syntax; specific hints listed above shipped in 12.0.

2. Legacy keyword form (still supported)

These sit as bare keywords between SELECT and the column list:

LIMIT, OFFSET, FETCH

Three syntaxes for pagination — all valid:

-- Comma form (offset first, count second):
SELECT … FROM t LIMIT 100, 10;

-- With explicit OFFSET keyword:
SELECT … FROM t LIMIT 10 OFFSET 100;

-- Standard SQL OFFSET … FETCH (since 10.6):
SELECT … FROM t ORDER BY id OFFSET 100 ROWS FETCH NEXT 10 ROWS ONLY;

Notes:

• LIMIT row_count OFFSET offset is the safer, less ambiguous form. The comma form puts offset first; the OFFSET keyword form puts count first.

• FETCH … WITH TIES (also since 10.6) returns extra rows that tie for the last spot — requires an ORDER BY clause or errors out (ER 4180).

• FIRST and NEXT are interchangeable; ROW and ROWS are interchangeable.

• LIMIT … ROWS EXAMINED N is MariaDB-specific:

  SELECT … FROM t WHERE … LIMIT 10 ROWS EXAMINED 100000;
  

  Stops the scan once N rows have been examined (not just returned). Useful as a safety bound during exploration, or to enforce a soft SLA on a query.

Row Locking — FOR UPDATE, LOCK IN SHARE MODE

SELECT … FROM t WHERE … FOR UPDATE       [WAIT n | NOWAIT | SKIP LOCKED];
SELECT … FROM t WHERE … LOCK IN SHARE MODE [WAIT n | NOWAIT | SKIP LOCKED];

FOR UPDATE acquires write locks; LOCK IN SHARE MODE acquires read locks. The standard SQL synonym FOR SHARE is also accepted.

Worker-queue idiom (the canonical SKIP LOCKED use case):

START TRANSACTION;
SELECT id, payload FROM jobs
  WHERE status = 'pending'
  ORDER BY priority, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
-- ... process the job, then UPDATE its status ...
COMMIT;

Temporal Queries — FOR SYSTEM_TIME

Available on tables created WITH SYSTEM VERSIONING:

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP '2026-01-01 00:00:00';
SELECT * FROM t FOR SYSTEM_TIME BETWEEN ts1 AND ts2;
SELECT * FROM t FOR SYSTEM_TIME FROM ts1 TO ts2;
SELECT * FROM t FOR SYSTEM_TIME ALL;

The clause goes immediately after the table name (before any alias). The system variable system_versioning_asof applies a default FOR SYSTEM_TIME AS OF to every query in the session.

Full versioning model, transaction-precise vs. timestamp-precise variants, query-time-vs-transaction-time, etc.: see the mariadb-system-versioned-tables skill.

INTO OUTFILE, INTO DUMPFILE, INTO @var

SELECT … INTO OUTFILE 'file_name'
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS} [TERMINATED BY 's'] [[OPTIONALLY] ENCLOSED BY 'c'] [ESCAPED BY 'c']]
    [LINES [STARTING BY 's'] [TERMINATED BY 's']];

SELECT … INTO DUMPFILE 'file_name';

SELECT col1, col2 INTO @var1, @var2 FROM t WHERE …;

Common constraints (apply to both OUTFILE and DUMPFILE):

• The file must not exist. No overwrite, ever — delete or rotate beforehand.
• The connecting user needs the FILE global privilege.
• The path is constrained by secure_file_priv — if it's set (it usually is), files can only be written under that directory. secure_file_priv = NULL disables file I/O entirely.
• The file path is a string literal, not a variable. Workaround: build the statement dynamically and PREPARE/EXECUTE it.

ANSI placement of INTO OUTFILE (between the column list and FROM) is supported for simple SELECTs only. For a UNION or set operation, place INTO OUTFILE on a wrapping SELECT * FROM ( … ):

SELECT * INTO OUTFILE '/path/out.tsv'
FROM (SELECT * FROM t1 UNION SELECT * FROM t2);

WITH ROLLUP

SELECT country, year, SUM(sales) FROM sales
  GROUP BY country, year WITH ROLLUP;

Adds super-aggregate rows: one per group prefix, then a grand total. The super-aggregated columns appear as NULL in the extra rows.

Constraints and traps:

• Cannot be combined with ORDER BY. Use ASC / DESC on the GROUP BY columns to control ordering; super-aggregate rows always come last regardless.
• LIMIT applies after rollup rows are added — so LIMIT 4 on a rollup result may cut off the grand-total row.
• NULL ambiguity: super-aggregate rows have NULL in grouped columns. If your data also contains genuine NULLs in those columns, the result is ambiguous; use GROUPING() (where available) or an explicit sentinel during display.

Other MariaDB-specific Bits

See Also

• mariadb-system-versioned-tables — full FOR SYSTEM_TIME semantics, transaction-precise history, versioning-aware partitioning
• mariadb-create-table / mariadb-alter-table — shared column, index, and table-option surface
• Canonical references on mariadb.com/docs (consult only for edge cases not covered here):
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-outfile
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-into-dumpfile
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-offset-fetch
  • https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select-with-rollup

This page is: Copyright © 2026 MariaDB. All rights reserved.