Is this the proper way to do boolean test in SQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In SQL Server you would generally use  I don t know about other database engines   select   from users where active   0

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to boolean-expression