96

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?

Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
Eric
  • 1,858
  • 2
  • 16
  • 17
  • Yes, as was posted, boolean fields are usually typed 'bit', not 'int' – Davis May 13 '09 at 19:05
  • I assume you are really after nice-looking code, since you would no doubt be aware of that "active = 0" is a possible workaround. In the choice between "NOT active" and "active = 0", I wouldn't bother - if you necessarily need it to be explained, add a comment. (In case someone working with the code in the future doesn't understand the true/false<->1/0 relation, maybe that someone shouldn't touch your code, btw...) – Tomas Aschan May 13 '09 at 19:06
  • 1
    @Eric: In SQL a predicate needs to produce a Boolean result. A "where active" does not produce such a result, because even if 'active' was a BIT data type - a BIT is not a Boolean value, it's an integer value with a range of 0..1. So you *must* do a comparison of some sort to produce a Boolean. "where NOT (active = 1)" would work, but not "where NOT active". – Tomalak May 13 '09 at 19:50
  • Tomalak - you should have posted that comment as an answer! – womp May 13 '09 at 23:34
  • 1
    @Tomalak: "In SQL a predicate needs to produce a Boolean result" -- not quite. SQL exhibits three value logic i.e. TRUE, FALSE and UNKNOWN (consider that 'active' can be NULL). – onedaywhen May 14 '09 at 08:08
  • @onedaywhen: That's right. I always tended to consider FALSE and UNKNOWN as equivalent for all practical purposes. That (and knowing that comparisons to NULL/UNKNOWN will always fail) was sufficient for me so far. – Tomalak May 14 '09 at 19:00

7 Answers7

105

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 */
Jose Basilio
  • 50,714
  • 13
  • 121
  • 117
  • 18
    @JoseBasilio- Except in PostgreSQL: http://www.postgresql.org/docs/9.1/static/datatype-boolean.html – Yarin Jan 06 '14 at 15:53
  • In most DBs, fields can also be NULL. You may need to check for NULL as well if you don't configure the table with a default value for the active field. – IAmNaN Jul 23 '14 at 15:16
  • using SQLite within Rails (4) it made queries using 'f' or 't' (not as chars though). When using the query above it did not work. Though: ```SELECT “model".* FROM “model" WHERE “boolean_column" = ‘f'``` worked – Stefan Hendriks Oct 30 '14 at 11:05
34

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
Luc M
  • 16,630
  • 26
  • 74
  • 89
  • looked ALL over to find if it was expecting true or TRUE or 1, so your answer was very helpful – jpw Mar 07 '12 at 06:31
  • 5
    +1 More on PostgreSQL boolean options: http://www.postgresql.org/docs/9.1/static/datatype-boolean.html – Yarin Jan 06 '14 at 15:56
  • 1
    Aslo, Posgtres accepts OP's syntax: `where active`, `where not active`. See http://www.postgresql.org/docs/8.2/static/functions-logical.html – Loïc Faugeron Aug 20 '15 at 10:14
16

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'
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
12

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

select * from users where active = 0
Jonathan Allen
  • 68,373
  • 70
  • 259
  • 447
3

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.

stili
  • 674
  • 6
  • 8
2

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

peterh
  • 11,875
  • 18
  • 85
  • 108
Jordi Cabot
  • 8,058
  • 2
  • 33
  • 39
-2

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.

alexandros84
  • 321
  • 4
  • 14
  • 1
    This is not true. https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html: "These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true". – Moritz Sep 21 '18 at 15:06