Categories Software Related Stories SQL: The Universal Database Language -- 11/3/98 Next: Nulls and Three-Valued Logic

Continued from Defining Selection Criteria

The predicate in our previous example contains a comparison based on equality (LAST_NAME = 'Jones'), but SQL predicates can contain several other types of comparisons as well. The most common ones are

• = Equal to
• <>Not equal to
• < Less than
• > Greater than
• <= Less than or equal to
• >= Greater than or equal to

An example of a comparison not based on equality is given below:

SELECT * FROM EMPLOYEES
   WHERE SALARY > 50000 ;

This query will return only employees whose annual salary is greater than $50,000.00. (See Table 7.)

Logical connectors

Sometimes you need to qualify a SELECT statement with more than one predicate. For example, if you just wanted to see information about Davy Jones, the result in Table 6 would not be correct. To qualify a WHERE clause further, you can use the logical connectors AND, OR, and NOT. To retrieve only Davy Jones's employee record, you could enter the following:

SELECT * FROM EMPLOYEES
   WHERE LAST_NAME = 'Jones' AND FIRST_NAME = 'Davy' ;

In this case, two predicates are joined by the logical connector AND. For the entire expression to be true, both predicates must be true. If you want to qualify a SELECT statement so that either one or the other value is acceptable, use the OR connector:

SELECT * FROM EMPLOYEES
   WHERE LAST_NAME = 'Jones'
   OR LAST_NAME = 'Smith' ;

Sometimes the best way to formulate a predicate is to state it negatively. If you were interested in seeing the employee information for all but the Boston office, you could write the following query:

SELECT * FROM EMPLOYEES
   WHERE NOT (BRANCH_OFFICE = 'Boston') ;

The keyword NOT is followed by a comparison enclosed in parentheses. This negates the result. If an employee's branch office were in Boston, the enclosed predicate would return a value of true, but the NOT operator would reverse the value to false and the row would not be selected.

Predicates may be nested within other predicates. To ensure that they are evaluated in the correct order, use parentheses to group them:

SELECT * FROM EMPLOYEES
   WHERE  (LAST_NAME = 'Jones'
   AND FIRST_NAME = 'Indiana')
   OR (LAST_NAME = 'Smith'
   AND FIRST_NAME = 'Bessie') ;

SQL follows the standard mathematical convention for evaluating expressions: The expression within parentheses is evaluated first; other expressions are evaluated from left to right.

That wraps up logical connectors. But before we go on, let's update our SELECT statement syntax:

SELECT [DISTINCT] (column [ {,column} ] ) | *
   FROM table [ {, table} ]
   [ORDER BY column> [ASC] | DESC [{, column [ASC] | DESC } ] ]
   WHERE predicate [ { logical-connector predicate } ] ;

Next: Nulls and Three-Valued Logic

Published as PC Tech Feature in the 11/17/98 issue of PC Magazine.

Elsewhere on ZDNet
• Learn SQL Online -- ZD University
• Business Software: Databases -- ZD Products