92

How to write literal boolean value in SQL Server? See sample use:

select * from SomeTable where PSEUDO_TRUE

another sample:

if PSEUDO_TRUE
begin
  select 'Hello, SQL!'
end 

Note: The query above has nothing to do with how I'm going to use it. It is just to test the literal boolean.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
kazinix
  • 28,987
  • 33
  • 107
  • 157
  • 1
    No, just want to see if true works in where clause. First off, I don't know the literal for true and false. – kazinix Aug 24 '11 at 05:11
  • 4
    SQL Server doesn't have a Boolean data type nor the required operators `IS TRUE`, `IS UNKNOWN`, etc (though the SQL-99 Standard has both). A common workaround is to use a `CHAR(1)` column constrained `CHECK (col1 IN ('T', 'F'))`. – onedaywhen Aug 24 '11 at 08:48

13 Answers13

83

SQL Server doesn't have a boolean data type. As @Mikael has indicated, the closest approximation is the bit. But that is a numeric type, not a boolean type. In addition, it only supports 2 values - 0 or 1 (and one non-value, NULL).

SQL (standard SQL, as well as T-SQL dialect) describes a Three valued logic. The boolean type for SQL should support 3 values - TRUE, FALSE and UNKNOWN (and also, the non-value NULL). So bit isn't actually a good match here.

Given that SQL Server has no support for the data type, we should not expect to be able to write literals of that "type".

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 8
    I wonder what could have made the SQL Server team to have bit instead of boolean data type (with values true and false). Boolean is so natural across all application domains. I believe they would still be using 1 byte in storage to store the bit data type. Even with internal storage as bit they could have supported `True` and `False` as syntactic sugar in the SQL scripts at least. – RBT Aug 13 '16 at 00:56
  • Odds are because it's that way in C. There are no such values `true` or `false`, but instead truthy (not 0) and falsy (0) values. C99 added stdbool.h, which only defines the macros for these, but at post processing any such values are replaced with 1 and 0 respectively. – Dragas Mar 02 '20 at 10:11
48
select * from SomeTable where 1=1
nocache
  • 1,805
  • 16
  • 18
  • 1
    It works! Unfortunately it's not a literal, the result of `1=1` is boolean true but it's not literal. – kazinix Aug 24 '11 at 05:22
25

Most databases will accept this:

select * from SomeTable where true

However some databases (eg SQL Server, Oracle) do not have a boolean type. In these cases you may use:

select * from SomeTable where 1=1

BTW, if building up an sql where clause by hand, this is the basis for simplifying your code because you can avoid having to know if the condition you're about to add to a where clause is the first one (which should be preceded by "WHERE"), or a subsequent one (which should be preceded by "AND"). By always starting with "WHERE 1=1", all conditions (if any) added to the where clause are preceded by "AND".

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    `An expression of non-boolean type specified in a context where a condition is expected, near 'group'` I'm using MSSQL – kazinix Aug 24 '11 at 05:14
  • 4
    Dear downvoters: The initial question I answered did **not** specify the server type "sql-server", so I answered the generic question with a generic answer with the caveat of "most databases". I fail to see why this deserves a downvote. – Bohemian Aug 24 '11 at 09:08
  • 1
    You better remove your answer before you get any more downvotes. Somehow it's my fault that I just indicated `SQL` not `MSSQL`. – kazinix Aug 25 '11 at 02:19
  • 2
    @dpp is the answer acceptable now? I've given an alternative that *all* databases will accept – Bohemian Nov 24 '12 at 22:44
  • 2
    Every Microsoft SQL server is a SQL database server, buit not every SQL database server is a *Microsoft* SQL server. Just as every apple is a fruit, but not every fruit is an apple (or perhaps in MS case, a pear :) – Reversed Engineer Feb 19 '15 at 16:40
18

This isn't mentioned in any of the other answers. If you want a value that orms (should) hydrate as boolean you can use

CONVERT(bit, 0) -- false CONVERT(bit, 1) -- true

This gives you a bit which is not a boolean. You cannot use that value in an if statement for example:

IF CONVERT(bit, 0)
BEGIN
    print 'Yay'
END

woudl not parse. You would still need to write

IF CONVERT(bit, 0) = 0

So its not terribly useful.

Sam
  • 1,725
  • 1
  • 17
  • 28
16

According to Microsoft: syntax for searching is

[ WHERE <search_condition> ]*

And search condition is:

<search_condition> ::= 
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

And predicate is:

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 

As you can see, you always have to write two expressions to compare. Here search condition is boolean expression like 1=1, a!=b

Do not confuse search expressions with boolean constants like 'True' or 'False'. You can assign boolean constants to BIT variables

DECLARE @B BIT
SET @B='True'

but in TSQL you can not use boolean constants instead of boolean expressions like this:

SELECT * FROM Somewhere WHERE 'True'

It will not work.

But you can use boolean constants to build two-sided search expression like this:

SEARCH * FROM Somewhere WHERE 'True'='True' 
Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
Dalex
  • 3,585
  • 19
  • 25
11

SQL Server does not have literal true or false values. You'll need to use the 1=1 method (or similar) in the rare cases this is needed.

One option is to create your own named variables for true and false

DECLARE @TRUE bit
DECLARE @FALSE bit
SET @TRUE = 1
SET @FALSE = 0

select * from SomeTable where @TRUE = @TRUE

But these will only exist within the scope of the batch (you'll have to redeclare them in every batch in which you want to use them)

Daniel Renshaw
  • 33,729
  • 8
  • 75
  • 94
  • 1
    This doesn't work as explained in the answers above. "An expression of non-boolean type specified in a context where a condition is expected, near '@TRUE'" – Mike Chamberlain Aug 09 '12 at 02:47
  • 2
    +1 this worked for me in `case when exists( select 1 from project.quota_group_supplier qgs with (nolock) where qgs.project_quota_id=qg.project_quota_id) then @TRUE else @FALSE end ` – Maslow Jul 16 '14 at 13:45
6

You can use the values 'TRUE' and 'FALSE'. From https://learn.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql:

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Matt H
  • 7,311
  • 5
  • 45
  • 54
  • 1
    Could you clarify what you mean? It should work just fine in WHERE clauses. – Matt H Nov 27 '18 at 10:54
  • 2
    E.g. `select 'TRUE' where 'TRUE'`. Neither can it be used meaningfully in the select-context, nor does the where-clause work. The SQL-Parser complains about 'TRUE' not being boolean. Likewise `select TRUE where TRUE` is invalid in both parts. All in all. It does not generally work on MS-SQL. – Sebastian Mach Nov 27 '18 at 11:42
  • 1
    Ok, but it does work when comparing a value, such as `WHERE column = 'TRUE'` or `IF @value = 'FALSE'`. – Matt H Nov 28 '18 at 09:14
5

How to write literal boolean value in SQL Server?
select * from SomeTable where PSEUDO_TRUE

There is no such thing.

You have to compare the value with something using = < > like .... The closest you get a boolean value in SQL Server is the bit. And that is an integer that can have the values null, 0 and 1.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
2

You should consider that a "true value" is everything except 0 and not only 1. So instead of 1=1 you should write 1<>0.

Because when you will use parameter (@param <> 0) you could have some conversion issue.

The most know is Access which translate True value on control as -1 instead of 1.

Marco Guignard
  • 613
  • 3
  • 9
2

You can use 'True' or 'False' strings for simulate bolean type data.

Select *
From <table>
Where <columna> = 'True'

I think this way maybe slow than just put 1 because it's resolved with Convert_implicit function.

Fabio 2st
  • 21
  • 1
1

I question the value of using a Boolean in TSQL. Every time I've started wishing for Booleans & For loops I realised I was approaching the problem like a C programmer & not a SQL programmer. The problem became trivial when I switched gears.

In SQL you are manipulating SETs of data. "WHERE BOOLEAN" is ineffective, as does not change the set you are working with. You need to compare each row with something for the filter clause to be effective. The Table/Resultset is an iEnumerable, the SELECT statement is a FOREACH loop.

Yes, "WHERE IsAdmin = True" is nicer to read than "WHERE IsAdmin = 1"

Yes, "WHERE True" would be nicer than "WHERE 1=1, ..." when dynamically generating TSQL.

and maybe, passing a Boolean to a stored proc may make an if statement more readable.

But mostly, the more IF's, WHILE's & Temp Tables you have in your TSQL, the more likely you should refactor it.

David Lean
  • 123
  • 1
  • 4
1

I hope this answers the intent of the question. Although there are no Booleans in SQL Server, if you have a database that had Boolean types that was translated from Access, the phrase which works in Access was "...WHERE Foo" (Foo is the Boolean column name). It can be replaced by "...WHERE Foo<>0" ... and this works. Good luck!

den232
  • 682
  • 6
  • 14
-1
select * from SomeTable where null is null

or

select * from SomeTable where null is not null

maybe this is the best performance?

sergey
  • 11