CODEWITHSUNDEEP
sqlboolean-expression
Eric
Eric

Reputation: 2018

Is this the proper way to do boolean test in SQL?

Assume active is a "boolean field" (tiny int, with 0 or 1)

-- Find all active users
select * from users where active 

-- Find all inactive users
select * from users where NOT active

In words, can the "NOT" operator be applied directly on the boolean field?

Upvotes: 107

Views: 384087

Answers (6)

Jordi Cabot
Jordi Cabot

Reputation: 8228

PostgreSQL supports boolean types, so your SQL query would work perfectly in PostgreSQL.

Upvotes: 3

stili
stili

Reputation: 674

I personally prefer using char(1) with values 'Y' and 'N' for databases that don't have a native type for boolean. Letters are more user frendly than numbers which assume that those reading it will now that 1 corresponds to true and 0 corresponds to false.

'Y' and 'N' also maps nicely when using (N)Hibernate.

Upvotes: 3

Luc M
Luc M

Reputation: 17314

With Postgres, you may use

select * from users where active

or

select * from users where active = 't'

If you want to use integer value, you have to consider it as a string. You can't use integer value.

select * from users where active = 1   -- Does not work

select * from users where active = '1' -- Works

Upvotes: 37

Scott Ivey
Scott Ivey

Reputation: 41568

MS SQL 2008 can also use the string version of true or false...

select * from users where active = 'true'
-- or --
select * from users where active = 'false'

Upvotes: 17

Jose Basilio
Jose Basilio

Reputation: 51488

A boolean in SQL is a bit field. This means either 1 or 0. The correct syntax is:

select * from users where active = 1 /* All Active Users */

or

select * from users where active = 0 /* All Inactive Users */

Upvotes: 113

Jonathan Allen
Jonathan Allen

Reputation: 70317

In SQL Server you would generally use. I don't know about other database engines.

select * from users where active = 0

Upvotes: 13

Related Questions