SQL Booleans — The True Will Set You Free

Relational databases store information in tables — in columns that describe aspects of an item and rows which tie together the columns. The SQL Boolean algebra operations — AND, OR, NOT — operate on true and false values (in contrast with elementary algebra, which deals with numbers).

Whereas addition and subtraction, for example, operate upon numbers, some aspects of the world deal with the either-or nature of truth. It is true that a blue whale is bigger than a minnow. It is false that dinosaurs and humans lived contemporaneously. Computing systems need some special way to represent the truth of things.

This blog entry will cover the BOOLEAN data type and the AND, OR, and NOT operators.

SQL Boolean Variables

Database implementations vary in syntax; the Boolean data type is usually written  as a BOOL or BOOLEAN. Early systems provided only number variables, so some databases have their boolean data type as merely an alias for INTEGER. False here is zero and true is one (or anything non-zero), 

BOOLEAN HappyCustomer ;

Create a boolean variable with syntax that parallels creating integer variables:

CREATE TABLE reality (
CustomerID INTEGER UNIQUE,
HappyCustomer BOOLEAN
);

Boolean values are the keywords true and false:

INSERT INTO
reality
VALUES
( 1, TRUE ), ( 2, FALSE ) ;

The SQL Select operator will return Boolean values:

SELECT HappyCustomer FROM reality ;

Boolean values are usually case-insensitive, with TRUE, True, and true being synonymous. Your database will probably display Boolean values consistently in only one case.

Boolean Values Returned From Comparisons

Boolean values are all around, even when not explicitly declared as variables. For example, the arithmetic comparison ( 3 > 2 ) — three is greater than two — returns a boolean false. This is how conditional statements work; the arithmetic comparison is done and the execution branch is chosen based on the resulting truth value.

IF
  ( wait_time > 20 )
THEN
  HappyCustomer = false;
ELSE
  HappyCustomer = true ;
END IF;

Boolean Operators

Boolean operators — AND, OR, and NOT — operate on boolean values. (Boolean operators “logically” compare while numeric operators “arithmetically” compare.)

Boolean AND and OR are “binary” — that is to say they operate on two arguments, while NOT is “unary” — operating on one argument.

Boolean AND

Boolean AND — also known as conjunction — returns true if both arguments themselves are true. SQL statements with multiple Boolean operations compare arguments logically two at a time, from left to right.

IF brilliant AND tortured AND poor
THEN artiste = 'genius' ; END IF ;

Note that the above is syntactic sugar for the following, more verbose, statement:

IF ( brilliant = true ) AND ( tortured = true ) AND ( poor = true )
THEN artist = 'genius' ; END IF ;

Boolean OR

Boolean OR — also known as disjunction — returns true if either argument is true.

IF piquant OR spicy OR zesty OR pickled OR brandied
THEN savory = true ; END IF ;

Boolean NOT

Boolean NOT — also known as negation — returns the logical opposite of its one argument. 

IF NOT ( WantsGood AND WantsFast AND WantsCheap )
THEN possible = true ; END IF ;

Other Boolean Operators

In the interest of completeness, mention ought be made of some of the other binary boolean operations:

NAND returns false if both inputs are true, otherwise true.

NOR returns true if both inputs are false, otherwise false.

XOR — exclusive OR — returns false if both of the inputs are either false or true, otherwise true.

XNOR — exclusive NOR — returns true when both the inputs are either false or true, otherwise false.

Conclusion

Covered here has been an overview of Boolean (logical) vs algebraic (numerical) arithmetic, the SQL Boolean data type, the syntax and use of the Boolean AND, OR, and NOT operators, and mention made of the extended logical operations outside of core SQL.

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE reality (
  CustomerID INTEGER UNIQUE,
  HappyCustomer BOOLEAN
);

INSERT INTO
  reality
VALUES
  ( 1, TRUE ), ( 2, FALSE ) ;