SQLite IF Exists Clause

Question

How to write IF EXISTS as shown in below query in SQLite? I read somewhere that IF clause doesn't exist in SQLite. What would be a better alternative for this?

if exists (select username from tbl_stats_assigned where username = 'abc' )
    select 1 as uname
else
    select 0 as uname

You're going to need a table which contains all the usernames whether assigned or not (in this case, a table listing all users). You cannot get a record returned in a result set for a value that does not exist is some table somewhere in the database. — Larry Lustig, Oct 12 '11 at 20:52
Thanks Larry. Here, I am not trying to retrieve a record just checking if a match is found. — thinkster, Oct 12 '11 at 20:59
Then execute a SELECT statement against that table in whatever language you are using to talk to the database. Check the return result set to determine whether it has a row or not. No row is means no user in the table. — Larry Lustig, Oct 12 '11 at 21:01

score 22 · Accepted Answer · answered Oct 12 '11 at 21:19

Just do it the standard SQL way:

select exists(
    select 1
    from tbl_stats_assigned
    where username = 'abc'
);

Assuming of course that your 1 and 0 are actually boolean values (which SQLite represents with one and zero just like MySQL).

That should work in any SQL database and some even have special optimizations to support that idiom.

SQLite IF Exists Clause

1 Answers1

Linked