CODEWITHSUNDEEP
sqlsqlite
peterchen
peterchen

Reputation: 41106

SQLite: detect if a rowid exists

What's the best/right/fastest/most appropriate way to detect if a row with a given rowid exists?

Or by extension, hwo to detect if at least one row matching a given condition exists?

I'm firing quite some of these requests. I am currently using 

SELECT 1 FROM table WHERE condition LIMIT 1

looks a bit weird to me, but looks to me like "the least work" for the db, however, my SQL knowledge is spotty.

Upvotes: 2

Views: 2502

Answers (5)

ejbaum
ejbaum

Reputation: 73

Since it's sqlite, you need to use the column name "rowid" to access that id column. Using Craig Ringer's sql, the sqlite version would look like this: 

SELECT EXISTS(SELECT 1 FROM table WHERE rowid = insert_number)

Upvotes: 2

Araw
Araw

Reputation: 2478

You can for example use 

SELECT COUNT(*) FROM table WHERE ID = whatever

Upvotes: 0

arvind
arvind

Reputation: 1493

One could test 

select true from table where id = id1

Upvotes: 0

GarethD
GarethD

Reputation: 69769

Use EXISTS, it sounds perfect for what you are after. e.g.

SELECT *
FROM T1
WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.X = T1.X AND T2.Y = 1)

It is effectly the same as LIMIT 1 but is generally optimised better.

Upvotes: 1

Arion
Arion

Reputation: 31239

I would probably do it something like this:

SELECT
    CASE 
        WHEN EXISTS(SELECT NULL FROM table1 WHERE ID=someid)
        THEN 1
        ELSE 0
    END

To Count the rows is not that effective.

To check if something exists is in most cases more effective

Upvotes: 3

Related Questions