Sometimes you want to query to see if an item exists but if it doesn’t you usually get an empty result in SQL.  It would be more useful if it would return a 0.

I often want to ask the question: Does x exist? and get back a yes or no.  Yes or No often translates to boolean as a 1 or 0.  Let say I query to see if a Date is a Holiday.  I have a table of Holidays.

I want to see if today is a holiday so i enter the following:

Select case when count(HolidayKey) > 0 then 1 else 0 end as IsHoliday
 from dbo.Holidays
 where date = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

The case statement returns the results I am looking for.   If the date is Holiday then the count of Holidaykey is greater than or equal to 1, which returns a 1.  If the date doesn’t exist in the table the count function returns a  zero.

Without the count, the case statement fails to return the proper result. See below.