The WHERE clause: specifying rows

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:

Comparison operators (=, <, >, and so on) For example, you can list all employees earning more than $50,000:
```
SELECT emp_lname
FROM employee
WHERE salary > 50000
```
Ranges (BETWEEN and NOT BETWEEN) For example, you can list all employees earning between $40,000 and $60,000:
```
SELECT emp_lname
FROM employee
WHERE salary BETWEEN 40000 AND 60000
```
Lists (IN, NOT IN) For example, you can list all customers in Ontario, Quebec, or Manitoba:
```
SELECT company_name , state
FROM customer
WHERE state IN( 'ON', 'PQ', 'MB')
```
Character matches (LIKE and NOT LIKE) For example, you can list all customers whose phone numbers start with 415. (The phone number is stored as a string in the database):
```
SELECT company_name , phone 
FROM customer
WHERE phone LIKE '415%'
```
Unknown values (IS NULL and IS NOT NULL) For example, you can list all departments with managers:
```
SELECT dept_name
FROM Department
WHERE dept_head_id IS NOT NULL
```
Combinations (AND, OR) For example, you can list all employees earning over $50,000 whose first name begins with the letter A.
```
SELECT emp_fname,  emp_lname
FROM employee
WHERE salary > 50000
AND emp_fname like 'A%
```

In addition, the WHERE keyword can introduce the following:

Transact-SQL join conditions Joins are discussed in Joins: Retrieving Data from Several Tables.

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

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

Sort orders In comparing character data, < means earlier in the sort order and > means later in the sort order. The sort order is determined by the collation chosen when the database is created. You can find out the collation by running the dbinfo command-line utility against the database:
```
dbinfo -c "uid=dba;pwd=sql"
```
You can also find the collation from Sybase Central. It is on the Extended Information tab of the database property sheet.
Trailing blanks When you create a database, you indicate whether trailing blanks are to be ignored or not for the purposes of comparison.

By default, databases are created with trailing blanks not ignored. For example, 'Dirk' is not the same as 'Dirk '. You can create databases with blank padding, so that trailing blanks are ignored. Trailing blanks are ignored by default in Adaptive Server Enterprise databases.
Comparing dates In comparing dates, < means earlier and > means later.
Case sensivitivity When you create a database, you indicate whether string comparisons are case sensitive or not.

By default, databases are created case insensitive. For example, 'Dirk' is the same as 'DIRK'. You can create databases to be case sensitive, which is the default behavior for Adaptive Server Enterprise databases.

Here are some SELECT statements using comparison operators:

SELECT * 
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

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:

Enter the following query:
```
SELECT name, unit_price
FROM product
WHERE unit_price BETWEEN 10 AND 15
```
name

unit_price

Tee Shirt

14.00

Tee Shirt

14.00

Baseball Cap

10.00

Shorts

15.00

name	unit_price
Tee Shirt	14.00
Tee Shirt	14.00
Baseball Cap	10.00
Shorts	15.00

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:

Enter the following query:
```
SELECT name, unit_price
FROM product
WHERE unit_price NOT BETWEEN 10 AND 15
```
name

unit_price

Tee Shirt

9.00

Tee Shirt

9.00

Visor

7.00

Visor

7.00

Sweatshirt

24.00

Sweatshirt

24.00

name	unit_price
Tee Shirt	9.00
Tee Shirt	9.00
Visor	7.00
Visor	7.00
Sweatshirt	24.00
Sweatshirt	24.00

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.

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:

Symbols

Meaning

%

Matches any string of 0 or more characters

_

Matches any one character

[specifier]

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.

[^specifier]

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.

Symbols	Meaning
%	Matches any string of 0 or more characters
_	Matches any one character
[specifier]	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.
[^specifier]	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.

Examples

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

Description

Returns

'Mc%'

Search for every name that begins with the letters Mc

McEvoy

'%er'

Search for every name that ends with er

Brier, Miller, Weaver, Rayner

'%en%'

Search for every name containing the letters en.

Pettengill, Lencki, Cohen

'_ish'

Search for every four-letter name ending in ish.

Fish

'Br[iy][ae]r'

Search for Brier, Bryer, Briar, or Bryar.

Brier

'[M-Z]owell'

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

Powell

'M[^c]%'

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

Moore, Mulley, Miller, Masalsky

Match expression	Description	Returns
'Mc%'	Search for every name that begins with the letters Mc	McEvoy
'%er'	Search for every name that ends with er	Brier, Miller, Weaver, Rayner
'%en%'	Search for every name containing the letters en.	Pettengill, Lencki, Cohen
'_ish'	Search for every four-letter name ending in ish.	Fish
'Br[iy][ae]r'	Search for Brier, Bryer, Briar, or Bryar.	Brier
'[M-Z]owell'	Search for all names ending with owell that begin with a single letter in the range M to Z.	Powell
'M[^c]%'	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%'

Using NOT LIKE

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%'

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:

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?"'

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:

Default If no data is entered, and the column has no other default setting, NULL is entered.
Explicit entry You can explicitly enter the value NULL by typing the word NULL (without quotation marks).

If the word NULL is typed in a character column with quotation marks, it is treated as data, not as a null value.

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:

SELECT *
FROM department

dept_id

dept_name

dept_head_id

100

R & D

501

200

Sales

904

300

Finance

1293

400

Marketing

1576

500

Shipping

703

201

Eastern Sales

(NULL)

202

Western Sales

(NULL)

dept_id	dept_name	dept_head_id
100	R & D	501
200	Sales	904
300	Finance	1293
400	Marketing	1576
500	Shipping	703
201	Eastern Sales	(NULL)
202	Western Sales	(NULL)

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:

expression IS NULL
expression = NULL
expression = x where x is a variable or parameter containing NULL. This exception facilitates writing stored procedures with null default parameters.
expression != n where n is a literal not containing NULL and expression evaluates to NULL.

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

expression IS NOT NULL
expression != NULL
expression != x

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

Properties of NULL

The following list expands on the properties of NULL.

The difference between FALSE and UNKNOWN Although neither FALSE nor UNKNOWN returns values, there is an important logical difference between FALSE and UNKNOWN, because the opposite of false ("not false") is true. For example,
```
1 = 2
```
evaluates to false and its opposite,
```
1 != 2
```
evaluates to true. But "not unknown" is still unknown. If null values are included in a comparison, you cannot negate the expression to get the opposite set of rows or the opposite truth value.
Substituting a value for NULLs Use the isnull built-in function to substitute a particular value for nulls. The substitution is made only for display purposes; actual column values are not affected. The syntax is:

isnull ( expression, value )

For example, use the following statement to select all the rows from test, and display all the null values in column t1 with the value unknown.
```
SELECT ISNULL(t1, 'unknown') 
FROM test
```
Expressions that evaluate to NULL An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands are null. For example:
```
1 + column1
```
evaluates to NULL if column1 is NULL.
Concatenating strings and NULL If you concatenate a string and NULL, the expression evaluates to the string. For example:
```
SELECT 'abc' || NULL || 'def'
```
returns the string abcdef.

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.

SELECT *
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.

SELECT * 
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:

SELECT * 
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:

SELECT * 
FROM contact 
WHERE ( city = 'Lexington' 
        OR city = 'Burlington' ) 
   AND state = 'MA'