[sql] SQL Server - boolean literal?

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.

This question is related to sql sql-server boolean literals

The answer is


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.


select * from SomeTable where null is null

or

select * from SomeTable where null is not null

maybe this is the best performance?


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.


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)


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!


You can use the values 'TRUE' and 'FALSE'. From https://docs.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.


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' 

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.


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


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.


select * from SomeTable where 1=1

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.


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to boolean

Convert string to boolean in C# In c, in bool, true == 1 and false == 0? Syntax for an If statement using a boolean Truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all() Ruby: How to convert a string to boolean Casting int to bool in C/C++ Radio Buttons ng-checked with ng-model How to compare Boolean? Convert True/False value read from file to boolean Logical operators for boolean indexing in Pandas

Examples related to literals

python ValueError: invalid literal for float() SQL Server - boolean literal? Empty set literal? How do you specify a byte literal in Java? Java: how do I get a class literal from a generic type? Setting Short Value Java Can I escape a double quote in a verbatim string literal? What does the M stand for in C# Decimal literal notation? escaping question mark in regex javascript Why does instanceof return false for some literals?