OFFSET and FETCH is the SQL Standard method of paging, and the standardized way to do LIMIT OFFSET

The OFFSET element specifies the number of rows to skip before it starts to return rows from the source query. The FETCH element specifies the number of rows to return after the OFFSET clause has been processed. Deterministic behavior requires that the ORDER BY clause contains a column or combination of columns that form a unique key for the data set.

SQL Standard

The SQL:2011 Spec a WITH TIES (F867) and the ability to specify PERCENT(F866). The query is defined as,

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

You can see here that <result offset clause> and the <fetch first clause> are both optional and stand by themselves, moving on

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
  <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

<simple value specification> ::=
  <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

RDBMS-Specific

PostgreSQL

The PostgreSQL implementation has this grammar.

SELECT
...
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

Oracle

The Oracle implementation has this grammar,

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

SQL Server 2012

The ORDER BY clause syntax is extended by an optional OFFSET sub-clause which is itself extended by a FETCH subclause. FETCH does not stand without OFFSET which does not stand without ORDER BY. Using FETCH excludes TOP.

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression }
            { ROW | ROWS } ONLY
    ]
}

More details can be found in the Books Online documentation.

MySQL/MariaDB

Neither of these support the standardized OFFSET/FETCH instead opting for the ad-hoc LIMIT/OFFSET,

[LIMIT {[offset,] row_count | row_count OFFSET offset}]