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

Answer 1

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.