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

Qualifying with the WHERE Clause

The WHERE clause allows you to provide Boolean (true or false) conditions that rows must satisfy to be included in the resulting row set. In practice, a SELECT statement will almost always contain at least one qualification via the WHERE clause.

For example, suppose that you want to see all of the books in Book Town's Computers section. The subject_id for the Computers subject is 4. Therefore, the WHERE clause can be applied with an equivalence operation (the = operator) to check for all books in the books table with a subject_id equal to 4. This is demonstrated in Example 4-32.

Example 4-32. A simple WHERE clause

booktown=# 
SELECT * FROM books

booktown-# 
         WHERE subject_id = 4;

  id   |        title         | author_id | subject_id
-------+----------------------+-----------+------------
 41472 | Practical PostgreSQL |      1212 |          4
 41473 | Programming Python   |      7805 |          4
 41477 | Learning Python      |      7805 |          4
 41478 | Perl Cookbook        |      7806 |          4
(4 rows)

The query in Example 4-32 returns only rows whose subject_id column matches the integer constant value of 4. Thus, only the four rows for computer books are returned, rather than the 15 rows shown by the simple query in Example 4-23.

The WHERE clause accepts numerous conditions, provided that they are joined by valid logical keywords (e.g., the AND, and OR keywords) and returns a single Boolean condition. For example, you may be interested in seeing all Book Town titles that fall under the Computers subject which are also by the author Mark Lutz, thus joining two conditions to narrow the focus of your query. Alternatively, you might be interested in seeing each of Book Town's titles that fall under either the Computers subject or the Arts subject, thereby joining two conditions to broaden the focus of your intended result set. Example 4-33 demonstrates each of these scenarios using the AND keyword and OR keyword, respectively.

Example 4-33. Combining conditions in the WHERE clause

booktown=# 
SELECT title FROM books

booktown-# 
             WHERE subject_id = 4

booktown-# 
             AND author_id = 7805;

       title
--------------------
 Programming Python
 Learning Python
(2 rows)

booktown=# 
SELECT title FROM books

booktown-# 
             WHERE subject_id = 4

booktown-# 
             OR subject_id = 0;

        title
----------------------
 Dynamic Anatomy
 Practical PostgreSQL
 Programming Python
 Learning Python
 Perl Cookbook
(5 rows)

The first SELECT statement in Example 4-33 combines one condition, which checks for titles in the Computers subject (with a subject_id of 4), with another condition, which checks if the author is Mark Lutz (with an author_id of 7805) via the AND keyword. The result is a smaller data set, constrained to two rows that fit both specified conditions.

The second SELECT statement in Example 4-33 combines the same first condition (books in the Computers subject) with a second condition: if the title falls under the Arts subject (with a subject_id of 0). The result is a slightly larger data set of five rows that matched at least one of these conditions.

WHERE conditions may be grouped together indefinitely, though after two conditions you may wish to group the conditions with parentheses. Doing so explicitly indicates how the conditions are interrelated. As a demonstration, the two statements in Example 4-34 have different effects based merely on the addition of parentheses.

Example 4-34. Grouping WHERE conditions with parentheses

booktown=# 
SELECT * FROM books

booktown-# 
         WHERE author_id = 1866

booktown-# 
         AND subject_id = 15

booktown-# 
         OR subject_id = 3;

  id  |        title         | author_id | subject_id
------+----------------------+-----------+------------
 4513 | Dune                 |      1866 |         15
 1234 | The Velveteen Rabbit |     25041 |          3
(2 rows)

booktown=# 
SELECT * FROM books

booktown-# 
         WHERE author_id = 1866

booktown-# 
         AND (subject_id = 15

booktown(# 
              OR subject_id = 3);

  id  | title | author_id | subject_id
------+-------+-----------+------------
 4513 | Dune  |      1866 |         15
(1 row)

The preceding example demonstrates two attempts to look up Book Town titles with an author_id of 1866. The titles also have a subject_id of either 15, or 3. As you can see from the first statement, when the three conditions are used without parentheses, the intent of the statement is ambiguous, and interpreted incorrectly. The addition of parentheses will cause the evaluations within parentheses to be considered before any surrounding condition.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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