Need BOOLEAN Result from SQL EXISTS Statement without using a WHERE Clause

Question

Is there any way in a simple query to return a Boolean value using an SQL EXISTS statement without using a WHERE clause?

All of the 2008 R2 SQL Server Books Online examples show another WHERE clause and two tables. Website examples show either a WHERE or an IF-THEN-ELSE in a procedure.

I was hoping to do the following on one table:

EXISTS (SELECT  cx.id  FROM fdd.admissions_view as cx    WHERE cx.id=1111 and cx.campus='MEXI')  

The SELECT statement works fine and returns the ID. I just want to add EXISTS to return a BOOLEAN, but the syntax above is not valid.

Can I do something like this? If so, what am I missing syntax-wise? If not, what other technique may work?

Please advise. Thanks.

Answer 1

Here is one which uses EXISTS with CASE WHEN ... THEN .. ELSE ... END, tested with MySQL and Oracle:

SELECT    CASE WHEN EXISTS      (SELECT  cx.id      FROM fdd.admissions_view as cx        WHERE cx.id=1111 and cx.campus='MEXI')   THEN 1    ELSE 0    END  FROM DUAL 

Update:

Found some related Q/A:

• Optimizing SELECT COUNT to EXISTS
• is it possible to select EXISTS directly as a bit?

Answer 2

How about something like

select case when count(cx.id) > 0 then 1 else 0 end   FROM fdd.admissions_view as cx    WHERE cx.id=1111 and cx.campus='MEXI' 

?