Is this the right way to run a logical test in SQL?

Question

Suppose the “boolean field” is active (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 to a logical field?

Answer 1

A boolean in SQL is a bit field. This means 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 */ 

Answer 2

With Postgres you can use

 select * from users where active 

or

 select * from users where active = 't' 

If you want to use an integer value, you should consider it as a string. You cannot use an integer value.

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

Answer 3

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' 

Answer 4

In SQL Server, you usually use. I do not know other database engines.

 select * from users where active = 0 

Answer 5

I personally prefer to use char (1) with the values ​​'Y' and 'N' for databases that don't have a native type for boolean. Letters are more users than numbers, which suggest that those who read it will now match 1, and 0 corresponds to false.

'Y' and 'N' are also displayed well when using (N) sleep mode.

Answer 6

PostgreSQL supports boolean types, so your SQL query will work fine in PostgreSQL.

Answer 7

If you are using SQLite3, be careful:

Only "t" or "f" is required. Not 1 or 0. Not TRUE OR FALSE.

Just studied hard.