Collection Contents Index The FROM clause: specifying tables CHAPTER 5.  Summarizing, Grouping, and Sorting Query Results pdf/chap4.pdf

User's Guide
   PART 1. Working with Databases
     CHAPTER 4. Queries: Selecting Data from a Table       

The WHERE clause: specifying rows

The WHERE clause in a SELECT statement specifies the search conditions for exactly which rows are retrieved. The general format is:

SELECT select_list
FROM table_list
WHERE search-condition

Search conditions, (also called qualifications or predicates), in the WHERE clause include the following:

In addition, the WHERE keyword can introduce the following:

For Info     The following sections describe how to use WHERE clauses. For a complete listing of search conditions, see Search conditions.

Top of page  Using comparison operators in the WHERE clause

You can use comparison operators in the WHERE clause. The operators follow the syntax:

WHERE expression comparison-operator expression

For Info     For a listing of comparison operators, see Comparison conditions. For a description of what an expression can be, see Expressions.

Notes on comparisons 

Here are some SELECT statements using comparison operators:

FROM product 
WHERE quantity < 20 
SELECT E.emp_lname, E.emp_fname 
FROM employee E
WHERE emp_lname > 'McBadden' 
SELECT id, phone 
FROM contact 
WHERE state  != 'CA'

The NOT operator 

The NOT operator negates an expression. Either of the following two queries will find all Tee shirts and baseball caps that cost $10 or less. However, note the difference in position between the negative logical operator (NOT) and the negative comparison operator (!>).

SELECT id, name, quantity 
FROM product
WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap') 
AND NOT unit_price > 10 
SELECT id, name, quantity
FROM product
WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap')
AND unit_price !> 10

Top of page  Using ranges (between and not between) in the WHERE clause

The BETWEEN keyword specifies an inclusive range, in which the lower value and the upper value are searched for as well as the values they bracket.

  To list all the products with prices between $10 and $15, inclusive:
  1. Enter the following query:

    SELECT name, unit_price
    FROM product
    WHERE unit_price BETWEEN 10 AND 15



    Tee Shirt


    Tee Shirt


    Baseball Cap




You can use NOT BETWEEN to find all the rows that are not inside the range.

  To list all the products cheaper than $10 or more expensive than $15:
  1. Enter the following query:

    SELECT name, unit_price
    FROM product
    WHERE unit_price NOT BETWEEN 10 AND 15



    Tee Shirt


    Tee Shirt










Top of page  Using lists in the WHERE clause

The IN keyword allows you to select values that match any one of a list of values. The expression can be a constant or a column name, and the list can be a set of constants or, more commonly, a subquery.

For example, without in, if you want a list of the names and states of all the contacts who live in Ontario, Manitoba, or Quebec, you can type this query:

SELECT company_name , state
FROM customer
WHERE state = 'ON' OR state = 'MB' OR state = 'PQ'

However, you get the same results if you use IN. The items following the IN keyword must be separated by commas and enclosed in parentheses. Put single quotes around character, date, or time values. For example:

SELECT company_name , state
FROM customer
WHERE state IN( 'ON', 'MB', 'PQ')

Perhaps the most important use for the IN keyword is in nested queries, also called subqueries.

Top of page  Matching character strings in the WHERE clause

The LIKE keyword indicates that the following character string is a matching pattern. LIKE is used with character, binary, or date and time data.

The syntax for like is:

{ WHERE | HAVING } expression [ NOT ] LIKE match-expression

The expression to be matched is compared to a match-expression that can include these special symbols:




Matches any string of 0 or more characters


Matches any one character


The specifier in the brackets may take the following forms:

  • Range     A range is of the form rangespec1-rangespec2, where rangespec1 indicates the start of a range of characters, the hyphen indicates a range, and rangespec2 indicates the end of a range of characters

  • Set     A set can be comprised of any discrete set of values, in any order. For example, [a2bR].

Note that the range [a-f], and the sets [abcdef] and [fcbdae] return the same set of values.


The caret symbol (^) preceding a specifier indicates non-inclusion. [^a-f] means not in the range a-f; [^a2bR] means not a, 2, b, or R.

You can match the column data to constants, variables, or other columns that contain the wildcard characters shown in the table. When using constants, you should enclose the match strings and character strings in single quotes.


All the following examples use LIKE with the last_name column in the Contact table. Queries are of the form:

SELECT last_name
FROM contact
WHERE last_name LIKE match-expression

The first example would be entered as

SELECT last_name
FROM contact
WHERE last_name LIKE 'Mc%'

Match expression




Search for every name that begins with the letters Mc



Search for every name that ends with er

Brier, Miller, Weaver, Rayner


Search for every name containing the letters en.

Pettengill, Lencki, Cohen


Search for every four-letter name ending in ish.



Search for Brier, Bryer, Briar, or Bryar.



Search for all names ending with owell that begin with a single letter in the range M to Z.



Search for all names beginning with M' that do not have c as the second letter

Moore, Mulley, Miller, Masalsky

Wildcards require LIKE 

Wildcard characters used without LIKE are interpreted as literals rather than as a pattern: they represent exactly their own values. The following query attempts to find any phone numbers that consist of the four characters 415% only. It does not find phone numbers that start with 415.

SELECT phone 
FROM Contact
WHERE phone = '415%'

Using LIKE with date and time values 

You can use LIKE on date and time fields as well as on character data. When you use LIKE with date and time values, the dates are converted to the standard DATETIME format, and then to VARCHAR.

One feature of using LIKE when searching for DATETIME values is that, since date and time entries may contain a variety of date parts, an equality test has to be written carefully in order to succeed.

For example, if you insert the value 9:20 and the current date into a column named arrival_time, the clause:

WHERE arrival_time = '9:20'

fails to find the value, because the entry holds the date as well as the time. However, the clause below would find the 9:20 value:

WHERE arrival_time LIKE '%9:20%'


With NOT LIKE, you can use the same wildcard characters that you can use with LIKE. To find all the phone numbers in the Contact table that do not have 415 as the area code, you can use either of these queries:

SELECT phone 
FROM Contact 
WHERE phone NOT LIKE '415%'
SELECT phone 
FROM Contact
WHERE NOT phone LIKE '415%'

Top of page  Character strings and quotation marks

When you enter or search for character and date data, you must enclose it in single quotation marks, as in the following example.

SELECT first_name, last_name 
FROM contact
WHERE first_name = 'John'

If the quoted_identifier database option is set to OFF (it is ON by default), you can also use double quotes around character or date data.

  To set the quoted_identifier option off for the current user ID:
  1. Type the following command:

    SET OPTION quoted_identifier = 'OFF'

The quoted_identifier option is provided for compatibility with Adaptive Server Enterprise. By default, the Adaptive Server Enterprise option is quoted_identifier OFF and the Adaptive Server Anywhere option is quoted_identifier ON.

Quotation marks in strings 

There are two ways to specify literal quotations within a character entry. The first method is to use two consecutive quotation marks. For example, if you have begun a character entry with a single quotation mark and want to include a single quotation mark as part of the entry, use two single quotation marks:

'I don''t understand.'

With double quotation marks (quoted_identifier OFF):

"He said, ""It is not really confusing.""" 

The second method, applicable only with quoted_identifier OFF, is to enclose a quotation in the other kind of quotation mark. In other words, surround an entry containing double quotation marks with single quotation marks, or vice versa. Here are some examples:

'George said, "There must be a better way."'
"Isn't there a better way?"  
'George asked, "Isn''t there a better way?"'

Top of page  Unknown Values: NULL

A NULL in a column means that the user or application has made no entry in that column. A data value for the column is unknown or not available

NULL does not mean the same as zero (numerical values) or blank (character values). Rather, NULL values allow you to distinguish between a deliberate entry of zero for numeric columns or blank for character columns and a non-entry, which is NULL for both numeric and character columns.

Entering NULL 

NULL can be entered in a column where NULL values are permitted, as specified in the create table statement, in two ways:

For example, the dept_head_id column of the department table allows nulls. You can enter two rows for departments with no manager as follows:

INSERT INTO department (dept_id, dept_name)
VALUES (201, 'Eastern Sales')
INSERT INTO department 
VALUES (202, 'Western Sales', null)

When NULLs are retrieved 

When NULLS are retrieved, displays of query results in Interactive SQL show (NULL) in the appropriate position:

FROM department





R & D















Eastern Sales



Western Sales


Top of page  Testing a column for NULL

You can use IS NULL in search conditions to compare column values to NULL and to select them or perform a particular action based on the results of the comparison. Only columns that return a value of TRUE are selected or result in the specified action; those that return FALSE or UNKNOWN do not.

The following example selects only rows for which unit_price is less than $15 or is NULL:

SELECT quantity , unit_price
FROM product 
WHERE unit_price < 15
OR unit_price IS NULL

The result of comparing NULL is UNKNOWN, since it is not possible to determine whether NULL is equal (or not equal) to a given value or to another NULL. The following cases return TRUE when expression is any column, variable or literal, or combination of these, which evaluates as NULL:

The negative versions of these expressions return TRUE when the expression does not evaluate to NULL:

Note that the far right side of these exceptions is a literal null, or a variable or parameter containing NULL. If the far right side of the comparison is an expression (such as @nullvar + 1), the entire expression evaluates to NULL.

There are some conditions that never return true, so that queries using these conditions do not return result sets. For example, the following comparison can never be determined to be true, since NULL means having an unknown value:

WHERE column1 > NULL

This logic also applies when you use two column names in a WHERE clause, that is, when you join two tables. A clause containing the condition

WHERE column1 = column2

does not return rows where the columns contain NULL.

You can also find NULL or non-NULL with this pattern:

WHERE column_name IS [NOT] NULL 

For example:

WHERE advance < $5000 OR advance IS NULL

Top of page  Properties of NULL

The following list expands on the properties of NULL.

Top of page  Connecting conditions with logical operators

The logical operators AND, OR, and NOT are used to connect search conditions in WHERE clauses.

Using AND 

The AND operator joins two or more conditions and returns results only when all of the conditions are true. For example, the following query finds only the rows in which the contact's last name is Purcell and the contact's first name is Beth. It does not find the row for Beth Glassmann.

FROM contact
WHERE first_name = 'Beth'
   AND last_name = 'Purcell'

Using OR 

The OR operator also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing variants of Elizabeth in the first_name column.

FROM contact
WHERE first_name = 'Beth'
   OR first_name = 'Liz'

Using NOT 

The NOT operator negates the expression that follows it. The following query lists all the contacts who do not live in California:

FROM contact
WHERE NOT state = 'CA' 

When more than one logical operator is used in a statement, AND operators are normally evaluated before OR operators. You can change the order of execution with parentheses. For example:

FROM contact 
WHERE ( city = 'Lexington' 
        OR city = 'Burlington' ) 
   AND state = 'MA' 
Top of page  

Collection Contents Index The FROM clause: specifying tables CHAPTER 5.  Summarizing, Grouping, and Sorting Query Results pdf/chap4.pdf