24

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.

radoh
  • 4,554
  • 5
  • 30
  • 45
blarson
  • 345
  • 1
  • 2
  • 7
  • There is no boolean in SQL Server. You mean bit ;-) – Bridge Jun 15 '12 at 00:39
  • @Bridge Yes -- boolean (having one of two states), as opposed to troolean, unless it's nullable ;-) –  Jun 15 '12 at 00:40
  • @pst Not thinking of [FILE NOT FOUND](http://thedailywtf.com/Articles/What_Is_Truth_0x3f_.aspx) are you? :P – Bridge Jun 15 '12 at 00:41
  • @Bridge Not that one in *particular*, no ;-) –  Jun 15 '12 at 00:42
  • 1
    @Bridge you've said that twice, what is your point? The OP doesn't want a boolean data type, he wants the boolean result from an expression. Are you saying `IF (1=1)` does not behave like a boolean? – Aaron Bertrand Jun 15 '12 at 02:56
  • @AaronBertrand Sorry, I've removed one of them. I don't think it was entirely clear what the OP wanted - whether he wanted the whole statement as a conditional `IF EXISTS (...) SELECT ...` or whether he wanted to return a 1 or 0 depending on whether any values were returned by the subquery. – Bridge Jun 15 '12 at 07:37
  • Possible duplicate of [Optimizing SELECT COUNT to EXISTS](http://stackoverflow.com/questions/5031471/optimizing-select-count-to-exists) – ryenus Nov 15 '16 at 22:49

5 Answers5

21

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:

Community
  • 1
  • 1
ryenus
  • 15,711
  • 5
  • 56
  • 63
  • This is the best answer, and sadly another instance that the last and least voted is the best answer, _sigh_. Upvoting! – xpt Apr 08 '18 at 12:53
  • Best answer indeed, in SQL Server I had to add a cast to bit so I could treat it as such in the C# code calling the stored procedure using this statement. – Bart Vanseer Jun 27 '21 at 02:29
  • 1
    Just adding that this baby also works well with SQL Server and PostgreSQL, so it has very good coverage. I also like the explicit integers rather than relying on potentially database-specific return values from EXISTS. – Jonas Nov 15 '22 at 13:41
17

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'

?

500 - Internal Server Error
  • 28,327
  • 8
  • 59
  • 66
  • I wonder if SQL Server is smart enough to stop after finding one record on `count(...) > 0`; e.g. if it is re-written internally. (In this case it likely can't be more than 1, but...) –  Jun 15 '12 at 02:40
  • 4
    @pst I don't think you should expect that optimization, or rely on it even if you observe it (or think you observe it) in some cases. I'd argue that EXISTS will always be no worse than COUNT, but often better. So unless the actual count is relevant, I would pick EXISTS every time. – Aaron Bertrand Jun 15 '12 at 04:21
  • @AaronBertrand I agree, largely because I think it is more semantically clear -- but it's still something interesting to muse over. I find the ability of a modern relational databases to "do the right thing very fast" quite amazing. –  Jun 15 '12 at 17:10
  • @pst meh, why? If `EXISTS` is always going to be as fast or faster, why not just always use `EXISTS` and pretend `COUNT` isn't an option except in those cases where you're not just looking for an on/off indicator? – Aaron Bertrand Jun 15 '12 at 17:12
12

Not totally sure what you mean by "return", but here are some ideas.

DECLARE @return BIT = 0;
IF EXISTS( SELECT  cx.id
    FROM fdd.admissions_view as cx  
    WHERE cx.id=1111 and cx.campus='MEXI' ) SET @return = 1;

Or:

IF EXISTS( SELECT  cx.id
    FROM fdd.admissions_view as cx  
    WHERE cx.id=1111 and cx.campus='MEXI' ) SELECT 1 AS returnValue
ThinkingStiff
  • 64,767
  • 30
  • 146
  • 239
6

The issue is that EXISTS is only valid syntax in certain grammar constructs. I am not aware the formal rules (which means I should go RTFM :-?), but EXISTS can be wrapped in case which will work when used as an expression:

set @r = case when exists (...) then 1 else 0 end

return case when exists (...) then 1 else 0 end

e.g.

return case when exists (SELECT 1 -- because it's as good as anything else
    FROM fdd.admissions_view as cx  
    WHERE cx.id=1111 and cx.campus='MEXI')
  then 1 else 0 end
  • 2
    Your first statement on its own isn't valid (`case when exists ...`). I recommend removing or improving it, otherwise you will inadvertently suggest that `case` is a statement, not an expression, and may be able to determine control of flow, like it does in other languages, something that a lot of greener users assume about T-SQL as well. – Aaron Bertrand Jun 15 '12 at 12:10
  • @AaronBertrand Thanks for the suggestion. When I wrote it I was thinking "in an expression", but you're correct in that it can be misleading. –  Jun 15 '12 at 16:39
2

Based on the answer by ryenus a solution that returns a bit result on MS SQL Server:

select 
    cast(
        case when exists
            (select id from fdd.admissions_view where id=1111 and campus = 'MEXI') 
        then 1 
        else 0 
        end 
    as bit) as Result

Without the cast this will return an int result.

Bart Vanseer
  • 348
  • 3
  • 6