Checking existence of a record in a SQL table using case statement

Question

The query below returns 'Found' when the records with ID exists in services table but does not return 'Not Found' when the record does not exists in the services table. I can't figure out why.

select case when  exists (select idaccount from services  where idaccount 
=s.idaccount )
 then 'Found'
 else 'NotFound'  end as GSO  
 from services s 
 where s.idaccount in ( 1421)

Answer 1

Your query will only ever return a row if it exists, so the case statement is redundant, you could just as well write

SELECT 'Found' FROM services s WHERE s.idaccount IN (1421)

Although it makes very little sense, you could write something like:

SELECT CASE
         WHEN EXISTS (SELECT 1 FROM services WHERE idaccount = 1421)
         THEN 'Found'

         ELSE 'NotFound'
       END

Note lack of FROM clause in the outermost SELECT. Quicker way to write the same thing:

SELECT COALESCE((SELECT 'Found' FROM services WHERE idaccount = 1421), 'NotFound')