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 — 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 — 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 — 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 — 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.
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.
SQL Sample Table Code
CREATE TABLE reality ( CustomerID INTEGER UNIQUE, HappyCustomer BOOLEAN ); INSERT INTO reality VALUES ( 1, TRUE ), ( 2, FALSE ) ;