The AND, OR, and NOT
keywords are PostgreSQL's Boolean operators. They are commonly used to join or invert conditions in a SQL statement, particularly
in the WHERE clause and the HAVING clause.
Table 5-7 illustrates the Boolean values returned for the
AND, OR, and NOT
keywords, with each possible value for a Boolean field (true, false, or NULL).
Table 5-7. The AND, OR, and NOT operators
|
a
|
b
|
a
AND
b
|
a
OR
b
|
NOT
a
|
NOT
b
|
|
true
|
true
|
true
|
true
|
false
|
false
|
|
true
|
false
|
false
|
true
|
false
|
true
|
|
true
|
NULL
|
NULL
|
true
|
false
|
NULL
|
|
false
|
false
|
false
|
false
|
true
|
true
|
|
false
|
NULL
|
false
|
NULL
|
true
|
NULL
|
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
NULL
|
Example 5-14 sequentially uses the OR
and AND keywords in two queries to combine a pair of conditions by which rows should be
retrieved. In the first query, if a book has either a cost of greater than thirty dollars, or is out of stock, its
information will be returned. As you can see from the result set, matching one or both of these conditions causes a row to
be returned.
The second query in Example 5-14 uses the same conditions, but combines
them with the AND keyword. This results in a stricter condition, as both criteria must be
met. As such, only one row is returned, since only one book is found which both has a cost of greater than thirty dollars,
and is out of stock.
Example 5-14. Combining comparisons with Boolean operators
booktown=#
SELECT isbn, cost, stock
booktown-#
FROM stock
booktown-#
WHERE cost > 30
booktown-#
OR stock = 0;
isbn | cost | stock
------------+-------+-------
0394900014 | 23.00 | 0
044100590X | 36.00 | 89
0451198492 | 36.00 | 0
0451457994 | 17.00 | 0
(4 rows)
booktown=#
SELECT isbn, cost, stock
booktown-#
FROM stock
booktown-#
WHERE cost > 30
booktown-#
AND stock = 0;
isbn | cost | stock
------------+-------+-------
0451198492 | 36.00 | 0
(1 row)
