SQL RELATIVE

What is the SQL RELATIVE keyword used for?

RELATIVE tells a FETCH statement to return the row located a specified number of rows before or after the current cursor position.

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 RELATIVE: PostgreSQL, SQL Server (T-SQL), IBM DB2, SAP HANA. Not supported in MySQL or SQLite. Oracle supports similar behavior only through the DBMS_SQL package, not with the RELATIVE keyword.

SQL RELATIVE Full Explanation

RELATIVE is a scroll-orientation keyword used inside a FETCH statement on a scrollable cursor. Instead of moving the cursor to a fixed position (ABSOLUTE) or reading sequentially (NEXT, PRIOR), RELATIVE shifts the cursor by an offset from its present row. A positive offset moves forward, a negative offset moves backward, and zero refetches the current row. If the requested offset positions the cursor before the first or after the last row, the fetch returns no data and the cursor is left positioned before first or after last respectively. RELATIVE may only be used on scrollable (not forward-only) cursors, and performance can degrade on large result sets because the database must count rows to reach the target. The keyword is part of the SQL-92 standard and is implemented in several major systems, though syntax details vary.

SQL RELATIVE Syntax

FETCH RELATIVE <offset_integer> FROM <cursor_name>;

SQL RELATIVE Parameters

offset_integer (INTEGER) - Number of rows to move relative to the current position (positive, negative, or 0)
cursor_name (identifier) - Name of an open scrollable cursor

Example Queries Using SQL RELATIVE

-- Declare and open a scrollable cursor
DECLARE order_cur SCROLL CURSOR FOR
SELECT id, customer_id, total
FROM orders
ORDER BY created_at;

OPEN order_cur;

-- Read the first row
FETCH NEXT FROM order_cur;
-- Jump ahead four rows
FETCH RELATIVE 4 FROM order_cur;
-- Go back two rows
FETCH RELATIVE -2 FROM order_cur;
-- Re-fetch current row
FETCH RELATIVE 0 FROM order_cur;

CLOSE order_cur;
DEALLOCATE order_cur;

Expected Output Using SQL RELATIVE

Each FETCH RELATIVE returns a single row (or an empty result if the target position is outside the result set) and moves the cursor accordingly

Use Cases with SQL RELATIVE

Paging through a result set without closing and reopening the cursor
Implementing custom scroll bars in client applications
Re-examining a previously read row after conditional logic
Skipping over a block of rows that failed validation

Common Mistakes with SQL RELATIVE

Using RELATIVE on a forward-only cursor (raises an error)
Forgetting to OPEN the cursor before fetching
Assuming negative offsets are supported when the dialect allows only positive values
Expecting RELATIVE 1 to behave like NEXT after fetching the last row

First Introduced In

SQL-92

Frequently Asked Questions

What databases support RELATIVE?

PostgreSQL, SQL Server, DB2, and SAP HANA implement RELATIVE in standard FETCH syntax. MySQL and SQLite do not.

Does RELATIVE affect performance?

Yes. Large offsets can force the database engine to scan multiple rows to reach the target, which can be slower than sequential NEXT fetches.

How do I fetch the previous row?

Use `FETCH RELATIVE -1 FROM cursor_name;` on a scrollable cursor.

What if I need the 100th row from the start?

Use `FETCH ABSOLUTE 100 FROM cursor_name;` instead of RELATIVE for direct positioning.