is it possible to select EXISTS directly as a bit?

Question

I was wondering if it's possible to do something like this (which doesn't work):

select cast( (exists(select * from theTable where theColumn like 'theValue%') as bit)

Seems like it should be doable, but lots of things that should work in SQL don't ;) I've seen workarounds for this (SELECT 1 where... Exists...) but it seems like I should be able to just cast the result of the exists function as a bit and be done with it.

score 310 · Accepted Answer · edited Nov 24 '18 at 09:49

310

No, you'll have to use a workaround.

If you must return a conditional bit 0/1 another way is to:

SELECT CAST(
   CASE WHEN EXISTS(SELECT * FROM theTable where theColumn like 'theValue%') THEN 1 
   ELSE 0 
   END 
AS BIT)

Or without the cast:

SELECT
   CASE
       WHEN EXISTS( SELECT 1 FROM theTable WHERE theColumn LIKE 'theValue%' )
            THEN 1 
       ELSE 0 
   END

edited Nov 24 '18 at 09:49

Dai

141,631
28
261
374

answered May 03 '10 at 17:53

Alex K.

171,639
30
264
288

20

You do not need the cast if you are storing the result into a Bit datatype because the cast is already implicit. – MikeTeeVee Apr 08 '13 at 10:22
4

Just tested out this technique, works great. The CAST to BIT is not necessary to retrieve the results from the query, tested with SQL Server 2008 R2. – Tore Aurstad Mar 31 '15 at 09:09
In my case the cast did MUST be removed – Sérgio S. Filho Mar 06 '18 at 17:56

score 54 · Answer 2 · answered May 03 '10 at 17:43

54

SELECT CAST(COUNT(*) AS bit) FROM MyTable WHERE theColumn like 'theValue%'

When you cast to bit

0 -> 0
everything else -> 1
And NULL -> NULL of course, but you can't get NULL with COUNT(*) without a GROUP BY

bit maps directly to boolean in .net datatypes, even if it isn't really...

This looks similar but gives no row (not zero) if no matches, so it's not the same

SELECT TOP 1 CAST(NumberKeyCOlumn AS bit) FROM MyTable WHERE theColumn like 'theValue%'

answered May 03 '10 at 17:43

gbn

422,506
82
585
676

4

But this doesn't use EXISTS at all. I wasn't asking how to workaround it, I can find workarounds, I was asking if there was some trick to using exists as bit that I wasn't aware of. – jcollum May 03 '10 at 17:56
6

This is not a workaround, it's one correct way set-wise. EXISTS is this workaround... And very clean, no? – gbn May 03 '10 at 17:59
So you're saying instead of EXISTS I should use this query? If so, then yes, it's a workaround. – jcollum May 03 '10 at 19:56
1

@jcollum: yes, or something like this. EXISTS is almost always IF EXISTS or WHERE EXISTS and not used for output like you're trying to do – gbn May 04 '10 at 04:43
15

EXISTS is more efficient than COUNT when checking for existence of a record - see http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx – Tahir Hassan Oct 11 '12 at 13:25
1

@TahirHassan: I know. See http://stackoverflow.com/a/3271464/27535 . And check the first comment for your link too: this matches my answer – gbn Oct 12 '12 at 07:15
10

Unlike `EXISTS`, `COUNT` will keep looking in the data for matching rows even after finding the first one because it needs to get count. – IsmailS Jun 11 '14 at 13:50
unnecessary counting can be eliminated by running this query as `SELECT TOP 1 CAST(COUNT(1) AS bit) FROM MyTable WHERE theColumn like 'theValue%'` – tecfield Feb 12 '18 at 18:42
Hot to get NULL with COUNT(*) with a GROUP BY in any sql statement? – fr0ga Apr 19 '18 at 15:29
This is really inefficient. -1 – Al Kepp Jul 16 '19 at 20:14

score 14 · Answer 3 · edited Sep 22 '17 at 13:53

14

You can use IIF and CAST

SELECT CAST(IIF(EXISTS(SELECT * FROM theTable 
                       where theColumn like 'theValue%'), 1, 0) AS BIT)

edited Sep 22 '17 at 13:53

ΩmegaMan

29,542
12
100
122

answered Aug 25 '16 at 17:44

Jaider

14,268
5
75
82

1

I like this, but it only works in SQL Server 2012 and up. Looks like IIF was added in 2012 – ja928 Sep 13 '16 at 14:41

score 12 · Answer 4 · answered Oct 21 '12 at 00:46

I'm a bit late on the uptake for this; just stumbled across the post. However here's a solution which is more efficient & neat than the selected answer, but should give the same functionality:

declare @t table (name nvarchar(16))
declare @b bit

insert @t select N'Simon Byorg' union select N'Roe Bott'


select @b = isnull((select top 1 1 from @t where name = N'Simon Byorg'),0)
select @b whenTrue

select @b = isnull((select top 1 1 from @t where name = N'Anne Droid'),0)
select @b whenFalse

score 6 · Answer 5 · edited Oct 27 '12 at 02:08

6

You can also do the following:

SELECT DISTINCT 1
  FROM theTable
 WHERE theColumn LIKE 'theValue%'

If there are no values starting with 'theValue' this will return null (no records) rather than a bit 0 though

edited Oct 27 '12 at 02:08

Bernard Dy

1,982
2
29
38

answered Apr 19 '12 at 05:47

Nelson

71
1
1

score 4 · Answer 6 · answered Mar 06 '18 at 23:52

4

SELECT IIF(EXISTS(SELECT * FROM theTable WHERE theColumn LIKE 'theValue%'), 1, 0)

answered Mar 06 '18 at 23:52

MEC

1,690
1
17
23

score 2 · Answer 7 · answered May 03 '10 at 17:36

2

No it isn't possible. The bit data type is not a boolean data type. It is an integer data type that can be 0,1, or NULL.

answered May 03 '10 at 17:36

Martin Smith

438,706
87
741
845

3

@bzlm Yes it can in SQLServer for well over 10 years. SQL Server 7.0 introduced it http://msdn.microsoft.com/en-us/library/aa237157%28SQL.80%29.aspx – Martin Smith May 03 '10 at 18:09
4

@bzlm - It sounds like you're clutching at straws and don't really know anything about SQL Server data types to me. The definition of bit in SQL Server is "An integer data type that can take a value of 1, 0, or NULL." http://msdn.microsoft.com/en-us/library/ms177603.aspx. This applies to columns and Transact SQL variables. Nowhere can a bit variable be used as a boolean in SQL with `IF(@TRUE)` for example nor vice-versa can a boolean expression be coerced into a bit. (With e.g. `SET @BitVariable = (1=1)`) – Martin Smith May 03 '10 at 20:24
1

I see where you're going, but the casting to bit wasn't so much the issue as the being able to select EXISTS directly. – jcollum May 06 '10 at 17:03

score 1 · Answer 8 · answered Dec 20 '18 at 08:31

1

Another solution is to use ISNULL in tandem with SELECT TOP 1 1:

SELECT ISNULL((SELECT TOP 1 1 FROM theTable where theColumn like 'theValue%'), 0)

answered Dec 20 '18 at 08:31

anar khalilov

16,993
9
47
62

score -1 · Answer 9 · edited May 03 '10 at 18:34

I believe exists can only be used in a where clause, so you'll have to do a workaround (or a subquery with exists as the where clause). I don't know if that counts as a workaround.

What about this:

create table table1 (col1   int null)
go
select 'no items',CONVERT(bit, (select COUNT(*) from table1) )   -- returns 'no items', 0
go
insert into table1 (col1) values (1)
go
select '1 item',CONVERT(bit, (select COUNT(*) from table1) )     --returns '1 item', 1
go
insert into table1 (col1) values (2)
go
select '2 items',CONVERT(bit, (select COUNT(*) from table1) )    --returns '2 items', 1
go
insert into table1 (col1) values (3)
go
drop table table1
go

What about case whens in a select? – lowerkey Feb 08 '13 at 15:45 — lowerkey, Feb 08 '13 at 15:45

is it possible to select EXISTS directly as a bit?

9 Answers9

Linked