SQL Server - boolean literal?

Question

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.

Answer 1

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.

Answer 2

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".

Answer 3

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.

Answer 4

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.

Answer 5

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!

Answer 6

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.

Answer 7

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' 

Answer 8

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.

Answer 9

select * from SomeTable where null is null

or

select * from SomeTable where null is not null

maybe this is the best performance?

Answer 10

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)

Answer 11

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".

Answer 12

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.

Answer 13

select * from SomeTable where 1=1