Is this the proper way to do boolean test in SQL?
SqlBoolean ExpressionSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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 */
Solution 2 - Sql
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
Solution 3 - Sql
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'
Solution 4 - Sql
In SQL Server you would generally use. I don't know about other database engines.
select * from users where active = 0
Solution 5 - Sql
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.
Solution 6 - Sql
PostgreSQL supports boolean types, so your SQL query would work perfectly in PostgreSQL.
Solution 7 - Sql
If u r using SQLite3 beware:
It takes only 't' or 'f'. Not 1 or 0. Not TRUE OR FALSE.
Just learned the hard way.