Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Logical Operators

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire