How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?

Question

Let's say you want do something along the following lines:

SELECT CASE 
    WHEN (SELECT COUNT(id) FROM table WHERE column2 = 4) > 0
    THEN 1 ELSE 0 END

Basically just return 1 when there's one or more rows in the table, 0 otherwise. There has to be a grammatically correct way to do this. What might it be? Thanks!

For testing existence, RedFilter has the answer. Generally, your query for testing counts is correct as it stands (as long as a valid table name is subbed for `table`. — RichardTheKiwi, Oct 30 '12 at 14:09
If you care about performance, you've accepted the wrong answer. Not sure why you would want to rush, or assume that the first answer must be the best solution. — Aaron Bertrand, Oct 30 '12 at 14:46

D'Arcy Rittich · Answer 1 · 2012-10-30T14:17:11.430

33

Question: return 1 when there's one or more rows in the table, 0 otherwise:

In this case, there is no need for COUNT. Instead, use EXISTS, which rather than counting all records will return as soon as any is found, which performs much better:

SELECT CASE 
    WHEN EXISTS (SELECT 1 FROM table WHERE column2 = 4)    
        THEN 1  
    ELSE 0 
END

edited Oct 30 '12 at 14:17

answered Oct 30 '12 at 13:57

D'Arcy Rittich

167,292
40
290
283

does it apply to complex query? – Steve Lam Sep 10 '19 at 07:35
1

@SteveLam You can use any valid `SELECT` query within the `EXISTS` clause. – D'Arcy Rittich Sep 10 '19 at 13:23

score 3 · Answer 2 · edited Sep 16 '21 at 03:32

3

Mahmoud Gammal posted an answer with an interesting approach. Unfortunately the answer was deleted due to the fact that it returned the count of records instead of just 1. This can be fixed using the sign function, leading to this more compact solution:

SELECT sign(count(*)) FROM table WHERE column2 = 4

I posted this because I find it an interesting approach. In production I'd usually end up with something close to RedFilter's answer.

edited Sep 16 '21 at 03:32

Nathan Hughes

94,330
19
181
276

answered Oct 30 '12 at 14:04

Cristian Lupascu

39,078
16
100
137

1

ISNULL is redundant on COUNT() which never returns NULL. – RichardTheKiwi Oct 30 '12 at 14:16
@RichardTheKiwi I should have realized that; thanks for the great tip! I've edited my answer. – Cristian Lupascu Oct 30 '12 at 14:22

score -9 · Accepted Answer · answered Oct 30 '12 at 13:59

-9

You could do this:

SELECT CASE WHEN COUNT(ID) >=1 THEN 1 WHEN COUNT (ID) <1 THEN 0 END FROM table WHERE Column2=4

Reference: http://msdn.microsoft.com/en-us/library/ms181765.aspx

answered Oct 30 '12 at 13:59

tempidope

823
1
12
29

StackOverflow really needs to let people go ahead and accept answers as soon as they start coming in, not make them wait 10 minutes. I'll accept yours in a minute. – Panzercrisis Oct 30 '12 at 14:04
3

Why count all records when merely checking the existence of any with `EXISTS` will suffice? – D'Arcy Rittich Oct 30 '12 at 14:05
Well, it was just an example of how it might be used. Thanks though, man; I didn't realize that keyword existed. Sorry if I focused on the example a little too much. – Panzercrisis Oct 30 '12 at 14:06
3

What was wrong with the OP's own query which is far less convoluted? – RichardTheKiwi Oct 30 '12 at 14:16
3

@Panzercrisis why, so you can accept a bad answer before people can correct it and show you the right way to do what you've asked to do? – Aaron Bertrand Oct 30 '12 at 14:43
2

@JKarthik Sorry, but this answer is the wrong idea. An expensive count is not necessary here, never mind trying to coerce the optimizer into performing it TWICE! – Aaron Bertrand Oct 30 '12 at 14:45
Guys, I sincerely want to accept both answers, and I might blame myself for over-emphasizing the example. But the real question that's in the title is a more general notion than checking existence. I agree that the situation got a little awkward on which answer to choose, but I had to emphasize what reflected the main question over the answer that was closer to what I've actually used in my own code. It's just fair. I have up-voted all three answers though, and due to the quality of RedFilter's answer, he's received quite a bit of reputation pretty quickly from it anyway. – Panzercrisis Oct 30 '12 at 15:39
2

Panzercrisis Agree with the rest (and the -8 votes). My answer isn't performance-tuned. Aaron Thanks for pointing it out, I agree with your point. All, Choice of accepting an answer lies with the OP. The votes, comments everything helps the OP make an informed decision. But whatever suits, suits. And I think my response was posted second , after RedFilter posted. My motiviation was to show that, you can move down the CASE comparison alongside COUNT() itself. – tempidope Oct 31 '12 at 05:54

How can you use COUNT() in a comparison in a SELECT CASE clause in Sql Server?

3 Answers3