How to get ROWID in SQLite?

sql

join

best-practices

data-retrieval

byAnton Shumikhin·Oct 25, 2024

To fetch the unique ROWID in SQLite, use the following query:

SELECT ROWID, * FROM table_name;

Just replace table_name with the actual name of your table. This will return a list including the ROWID for each row. The aliases rowid, oid, and _rowid_ are interchangeable, except in the case of an INTEGER PRIMARY KEY column.

Fetching rows based on conditions

You can extract rows meeting certain conditions with a WHERE clause in your query:

SELECT ROWID, * FROM table_name WHERE column_name = 'desired_value';

Where column_name is the name of your column and desired_value is the condition value.

Important points to note

The rowid won't appear when using PRAGMA table_info or .schema. It's an invisible column, but it's always there.
Even with AUTOINCREMENT, SQLite still uses rowid for maintaining uniqueness.
It's usually not advisable to change the rowid, as it could impact the relational data's integrity.

Understanding the magic of rowid

In SQLite, the ROWID acts like the address of a record. It's a smart mechanism for quick data retrieval. The intended role here is similar to the unique ID of a book in a library system, which speeds up the access to any book.

Exploiting ROWID in SQL queries

Deletion of specific rows

Got an unwanted row? Make it disappear like a magic trick:

DELETE FROM table_name WHERE ROWID = 3;

// Talk about cleaning out your bookshelf!

Updating specific rows

ROWID makes updating specific rows as easy as swapping out old books for new ones:

UPDATE table_name SET column_name = 'new_value' WHERE ROWID = 2;

// The "New Shelf, Who Dis?" maneuver 📚

Joining tables with ROWID

ROWID is like a universal friend-maker you can use when joining tables:

SELECT a.ROWID, b.*
FROM table_a a
JOIN table_b b ON b.foreign_key = a.ROWID;

// It's the "book club" of SQL, introducing books (tables) to each other 🤝

The "dos" and "don'ts" of using ROWID

Don'ts:

Don't use rowid for tables declared WITHOUT ROWID.
Avoid modifying rowid to prevent the rise of unexpected side effects.
Never consider rowid as permanent. It may change, especially after a VACUUM operation.

Dos:

Leverage the fast accessibility property of rowid.
Use ROWID for debugging processes. It can serve as a stethoscope to examine low-level data issues.
Do remember: rowid can be your last resort for recovering corrupted or lost data.