SQL FETCH

What is SQL FETCH?

Retrieves the next set of rows from an open cursor (or the result set when used with OFFSET) into host variables or the client.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FETCH: PostgreSQL, MySQL (cursor style only), SQL Server, Oracle, DB2, Snowflake, SQLite (OFFSET … FETCH only), MariaDB, Redshift

SQL FETCH Full Explanation

SQL FETCH is a cursor-control statement defined in the SQL standard. After you DECLARE a cursor and OPEN it, FETCH moves the cursor pointer and copies the current row (or block of rows) into target variables so the application can process them. Orientation options such as NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n control which row is fetched. Many databases also support a FETCH clause in SELECT statements (OFFSET … FETCH) for server-side pagination; although the syntax overlaps, the semantics differ: the SELECT version limits rows returned directly to the client instead of working through an application cursor.Key behaviors:- Requires an already OPEN cursor.- Advances the cursor unless the orientation is PRIOR, ABSOLUTE, or RELATIVE.- Returns a status (SQLSTATE or @@FETCH_STATUS) so the application can detect end-of-result.- Works inside transactions; the cursor sees a snapshot consistent with its isolation level.Caveats:- Attempting to FETCH past the end of the result set sets a not-found condition.- Scrollable (bi-directional) fetches need a SCROLL or INSENSITIVE cursor declaration.- Some drivers limit the maximum block size for bulk fetches (e.g., FETCH 100 ROWS).

SQL FETCH Syntax

-- Cursor style (Standard SQL)
FETCH [orientation] [FROM] <cursor_name> INTO <target_list>;

-- Common orientations
FETCH NEXT FROM cur INTO ...;
FETCH PRIOR FROM cur INTO ...;
FETCH FIRST FROM cur INTO ...;
FETCH LAST FROM cur INTO ...;
FETCH ABSOLUTE 10 FROM cur INTO ...;
FETCH RELATIVE -2 FROM cur INTO ...;

-- SELECT pagination style (ANSI SQL:2008+) 
SELECT *
FROM orders
ORDER BY order_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

SQL FETCH Parameters

cursor_name (identifier) - The name of an already OPEN cursor.
orientation (keyword) - NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n | n ROWS; controls which row(s) to fetch.
target_list (variables or columns) - One variable per column selected by the cursor.

Example Queries Using SQL FETCH

-- Declare, open, and iterate with FETCH
DECLARE cur_orders SCROLL CURSOR FOR
  SELECT order_id, customer_id, total
  FROM orders
  ORDER BY order_id;

OPEN cur_orders;
-- Fetch first row
FETCH FIRST FROM cur_orders INTO :v_order_id, :v_cust, :v_total;
-- Fetch next row
FETCH NEXT FROM cur_orders INTO :v_order_id, :v_cust, :v_total;
-- Jump ahead 5 rows
FETCH RELATIVE 5 FROM cur_orders INTO :v_order_id, :v_cust, :v_total;
CLOSE cur_orders;

-- Pagination without a cursor (SQL:2008 style)
SELECT order_id, total
FROM orders
ORDER BY order_id
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

Expected Output Using SQL FETCH

For each FETCH on the cursor, one row (or the specified block) is copied into the target variables, and the cursor pointer moves
When no further rows are available, the database returns a not-found condition (SQLSTATE 02000 or @@FETCH_STATUS = -1)
The SELECT OFFSET … FETCH example returns 25 rows starting at offset 50

Use Cases with SQL FETCH

Row-by-row processing in embedded SQL or stored procedures
Building scrollable result sets in GUI applications
Bulk loading with FETCH n ROWS for improved network efficiency
Implementing pagination in web APIs with OFFSET … FETCH

Common Mistakes with SQL FETCH

Forgetting to OPEN the cursor before FETCH
Using FETCH PRIOR on a non-scrollable cursor
Assuming OFFSET … FETCH is available in older database versions
Neglecting to CLOSE cursors, leading to resource leaks

First Introduced In

SQL-92 (cursor operations); OFFSET … FETCH added in SQL:2008

Frequently Asked Questions

What orientations does FETCH support?

Standard orientations include NEXT (default), PRIOR, FIRST, LAST, ABSOLUTE n, and RELATIVE n. Some databases add FORWARD n or BACKWARD n variants for block fetches.

How do I detect the end of a cursor?

After each FETCH, check the not-found indicator. In embedded SQL this is SQLSTATE '02000'; in T-SQL use `@@FETCH_STATUS`; in PL/pgSQL catch the `NO_DATA_FOUND` exception.

Can I fetch multiple rows at once?

Yes. Many databases accept `FETCH NEXT 100 ROWS FROM cur INTO ...` which reduces round-trips. Host variables must be declared as arrays or bulk-collect types.

Is OFFSET … FETCH better than LIMIT?

OFFSET … FETCH is portable across ANSI-compliant systems and supports the optional `FETCH WITH TIES` extension. LIMIT is shorter but proprietary, so prefer OFFSET … FETCH for compatibility.