Using SQLite

Logic Representations

SQL and SQLite have a fair number of logic operations. Many of the operators, such as <= (test for less-than or equal) perform some type comparison or search between parameter expressions and return a logic value—that is, true or false. A number of SQL commands use these logic values to control how commands are applied to the database. For example, the WHERE clause of a SELECT command ultimately computes a logic value to determine if a given row is included in the result set or not.

Despite the fact that logic values are commonly used, SQLite does not have a native logic datatype (such as a Boolean). Rather, logic values are expressed as integer values. Zero is used to represent false, while one (or any other nonzero integer value) is used to represent true.

For other datatypes, SQLite uses the standard conversion rules to translate a text or BLOB value into an integer, before considering if the value is true or false. This means that most nonnumeric strings (such as 'abc') will translate to false.

The one exception is NULL. Next to true and false, NULL is considered a third logic state. This necessitates the concept of three valued logic, or 3VL. For the purpose of three valued logic, NULL is considered to be an unknown or undefined state. For more details, see Three-Valued Logic. In general, this means that once a NULL is introduced into an expression, it tends to be propagated through the expression. Nearly all unary and binary operators will return NULL if ...