Return Boolean Value on SQL Select Statement

Question

How to return a boolean value on SQL Select Statement?

I tried this code:

SELECT CAST(1 AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)

And it only returns TRUE if the UserID exists on the table. I want it to return FALSE if the UserID doesn't exist on the table.

SQL Server does not support a Boolean type e.g. `SELECT WHEN CAST(1 AS BIT) THEN 'YES' END AS result` -- results in an error i.e. `CAST(1 AS BIT)` is not the same logical TRUE. — onedaywhen, Apr 30 '12 at 07:38

score 329 · Accepted Answer · answered Apr 30 '12 at 02:17

329

What you have there will return no row at all if the user doesn't exist. Here's what you need:

SELECT CASE WHEN EXISTS (
    SELECT *
    FROM [User]
    WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END

answered Apr 30 '12 at 02:17

Chad

7,279
2
24
34

3

why use asterisk, it is better if you use `1` instead of `*`. – Mar 13 '15 at 06:04
9

@robertpeter07 - The two are equivalent, but `*` is more idiomatic. See [this question](http://stackoverflow.com/q/1597442/1121833). – Chad Mar 13 '15 at 15:12
If using on a WHILE loop would I have to enclose it within braces {} right after the 'WHILE' ? – full_prog_full Mar 10 '16 at 16:06
Can you add a column name to the returned value? – xMetalDetectorx Oct 23 '17 at 22:18
3

@xMetalDetectorx This worked for me to add the column name (the `AS bool` part is very important): `CAST( CASE WHEN EXISTS ( SELECT * FROM mytable WHERE mytable.id = 1) THEN TRUE ELSE FALSE END AS bool) AS nameofmycolumn` – Lucio Mollinedo Nov 29 '18 at 23:48
Really an extremely minor point... but I would put the cast around the case rather than having to cast two expressions. – BVernon Feb 09 '21 at 06:34

score 41 · Answer 2 · edited May 22 '15 at 10:37

41

Possibly something along these lines:

SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT)
FROM dummy WHERE id = 1;

http://sqlfiddle.com/#!3/5e555/1

edited May 22 '15 at 10:37

Salman A

262,204
82
430
521

answered Apr 30 '12 at 02:20

cableload

4,215
5
36
62

6

This returns a string, not a boolean – OMG Ponies Apr 30 '12 at 02:24
It's a good practice to include a column name - SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT) as mycolumnname FROM dummy WHERE id = 1 – Diego Alves Nov 27 '17 at 14:24

score 27 · Answer 3 · answered Aug 15 '13 at 22:28

27

Given that commonly 1 = true and 0 = false, all you need to do is count the number of rows, and cast to a boolean.

Hence, your posted code only needs a COUNT() function added:

SELECT CAST(COUNT(1) AS BIT) AS Expr1
FROM [User]
WHERE (UserID = 20070022)

answered Aug 15 '13 at 22:28

Stewart

17,616
8
52
80

9

Doing the `Exists(` test is much faster than doing a `Count(1)` test on tables with large numbers of rows. – Scott Chamberlain Apr 03 '14 at 02:06
5

Probably. I made no claims to performance in my answer, just the minimal code change to achieve what the OP wanted. However, if column `UserID` is indexed (or is even the PK) surely you're going straight to the one unique row that exists (or not). – Stewart Apr 03 '14 at 10:23

score 19 · Answer 4 · answered May 10 '18 at 10:22

19

Use 'Exists' which returns either 0 or 1.

The query will be like:

SELECT EXISTS(SELECT * FROM USER WHERE UserID = 20070022)

answered May 10 '18 at 10:22

Ananthi

413
7
13

21

Error: "Incorrect syntax near the keyword 'EXISTS'." http://sqlfiddle.com/#!18/ef905/18 – JoePC Jul 16 '18 at 20:21
Would `SELECT 1` be faster than `SELECT *` in this case? – Vapid Aug 10 '20 at 11:45

score 11 · Answer 5 · edited Oct 13 '15 at 12:28

11

select CAST(COUNT(*) AS BIT) FROM [User] WHERE (UserID = 20070022)

If count(*) = 0 returns false. If count(*) > 0 returns true.

edited Oct 13 '15 at 12:28

ngrashia

9,869
5
43
58

answered Oct 07 '15 at 12:25

G.Noulas

442
6
10

score 7 · Answer 6 · answered Mar 02 '18 at 22:47

7

I do it like this:

SELECT 1 FROM [dbo].[User] WHERE UserID = 20070022

Seeing as a boolean can never be null (at least in .NET), it should default to false or you can set it to that yourself if it's defaulting true. However 1 = true, so null = false, and no extra syntax.

Note: I use Dapper as my micro orm, I'd imagine ADO should work the same.

answered Mar 02 '18 at 22:47

RandomUs1r

4,010
1
24
44

My favorite, most concise answer so far. Fiddle of all answers: http://sqlfiddle.com/#!18/ef905/18 – JoePC Jul 16 '18 at 20:21
"Seeing as a boolean can never be null (at least in .NET)." (bool?) is a nullable bool. – Andrew Dennison Jul 02 '20 at 22:50

score 4 · Answer 7 · answered Nov 29 '18 at 23:56

4

For those of you who are interested in getting the value adding a custom column name, this worked for me:

CAST(
    CASE WHEN EXISTS ( 
           SELECT * 
           FROM mytable 
           WHERE mytable.id = 1
    ) 
    THEN TRUE 
    ELSE FALSE 
    END AS bool) 
AS "nameOfMyColumn"

You can skip the double quotes from the column name in case you're not interested in keeping the case sensitivity of the name (in some clients).

I slightly tweaked @Chad's answer for this.

answered Nov 29 '18 at 23:56

Lucio Mollinedo

2,295
1
33
28

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'CAST'. Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'THEN'. – ShaneC Jun 26 '20 at 10:41
@ShaneC I tested this code on PostgreSQL 9.X and it worked fine. What server are you using? – Lucio Mollinedo Jun 29 '20 at 16:12

score 1 · Answer 8 · answered Oct 11 '18 at 12:07

Notice another equivalent problem: Creating an SQL query that returns (1) if the condition is satisfied and an empty result otherwise. Notice that a solution to this problem is more general and can easily be used with the above answers to achieve the question that you asked. Since this problem is more general, I am proving its solution in addition to the beautiful solutions presented above to your problem.

SELECT DISTINCT 1 AS Expr1
FROM [User]
WHERE (UserID = 20070022)

score 1 · Answer 9 · answered Apr 23 '20 at 07:34

1

DECLARE @isAvailable      BIT = 0;

IF EXISTS(SELECT 1  FROM [User] WHERE (UserID = 20070022))
BEGIN
 SET @isAvailable = 1
END

initially isAvailable boolean value is set to 0

answered Apr 23 '20 at 07:34

Chamin Thilakarathne

347
1
6
19

Return Boolean Value on SQL Select Statement

9 Answers9

Linked