Local SQL Part 4: The WHERE Clause

Written by Larry DiGiovanni

29 October 2001

In this fourth installment of Larry's SQL series, we learn how to apply selection criteria in an SQL query!Local SQL Part 4
The WHERE Clause
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com

Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the fourth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article will focus on the use of the WHERE clause in simple SQL SELECT statements.

Introduction

In a previous article, we discussed the general form of the SQL SELECT statement, including the most common clauses and predicates. We used the SQL SELECT and FROM clauses to query and return all rows of a table.

The WHERE clause of the SQL SELECT statement is used to determine which rows of the base table(s) are to be included in the result set. The WHERE clause is followed by a list of conditions which is evaluated for each row of the table(s) being queried. For rows in which the condition list evaluates to True, the row is included in the result set. For rows in which the condition list evaluates to False, the row is excluded from the result set.

As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database.

WHERE Example

A common type of condition found in a WHERE clause is one which tests for equality between a column value and some given literal expression. From our example database, we could find all customers based in California with the following query:

SELECT Company
  FROM customer
 WHERE State = 'CA'

Executing this query produces an ANSWER table consisting of a single column, Company, listing the company names of all of our California-based companies. The result set is equivalent to the result set produced by the following QBE query:

customer | Company     | State |
         | CheckPlus   | CA    |

In our SQL example, we used a test for an exact comparison (State = 'CA'). We can also test using other comparison operators. The below query lists the Item Description and List Price of all of our inventory items that list for more than $45.00.

SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice > 45

Which is equivalent to the QBE query:

parts | Description | ListPrice     |
      | CheckPlus   | CheckPlus >45 |

How about if we wanted to list items which were greater than $45 but less than $100? In QBE, this would look like:

parts | Description | ListPrice          |
      | CheckPlus   | CheckPlus >45,<100 |

In SQL, it would look like:

SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice > 45
   AND ListPrice < 100

Note the use of multiple WHERE conditions, separated by "AND". If we wanted items which were less than $45 or greater than $100, our QBE would look like:

parts | Description | ListPrice             |
      | CheckPlus   | CheckPlus <45 OR >100 |

Our SQL would look like:

SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice < 45
    OR ListPrice > 100

Again, we used multiple WHERE conditions, separated by "OR" this time.

More Complex WHERE Clauses

The below query lists orders that totaled more than $3000 or orders that were paid with Visa and totaled more than $2000.

SELECT orderno, PaymentMethod, itemstotal
  FROM orders
 WHERE itemstotal > 3000
    OR (itemstotal >2000 AND PaymentMethod = 'Visa')

Note the use of parentheses to group the terms of the WHERE clause.

Special Operators

Local SQL has special operators to handle common query requirements.

BETWEEN

Consider a SQL query that lists the description and list price of all of our parts which cost between $45 and $100, inclusive. We could write this query as:

SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice >= 45
   AND ListPrice <= 100

Or, we could use the BETWEEN operator:

SELECT Description, ListPrice
  FROM parts
 WHERE ListPrice BETWEEN 45 AND 100

Note that the BETWEEN operator is inclusive of the start and end criteria.

IN

Consider a SQL query that lists the names of customers from California, Hawaii or Florida:

SELECT name
  FROM customer
 WHERE State = 'CA'
    OR State = 'HI'
    OR State = 'FL'

Or, we could use the IN operator:

SELECT name
  FROM customer
 WHERE State IN ('CA','HI','FL')

IN can also reference columns of other tables or queries. We’ll explore that capability in a future article.

Summary

The WHERE clause of a SQL SELECT statement restricts the result set (ANSWER table) to rows meeting specific requirements by evaluating criteria lists. Where the criteria list evaluates to TRUE, the row is returned. The criteria lists typically consist of comparisons between table columns and literal values or calculated expressions. There are several SQL operators which may also be used.

Local SQL Part 5

< Prev		Next >

[ Back ]