Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Selecting rows: the where clause

The where clause in a select statement specifies the search conditions that determine which rows are retrieved. The general format is:

select select_list 
     from table_list 
     where search_conditions

Search conditions, or qualifications, in the where clause include:

Comparison operators (=, <, >, and so on)

  where advance * 2 > total_sales * price

Ranges (between and not between)

  where total_sales between 4095 and 12000

Lists (in, not in)
```
  where state in ("CA", "IN", "MD")
```
Character matches (like and not like)
```
  where phone not like "415%" 
```
Unknown values (is null and is not null)
```
  where advance is null
```

Combinations of search conditions (and, or)

where advance < 5000 or total_sales between 2000
   and 2500

The where keyword can also introduce:

Join conditions (see Chapter 4, "Joins: Retrieving Data from Several Tables")
Subqueries (see Chapter 5, "Subqueries: Using Queries Within Other Queries")
The only where condition that you can use on text columns is like (or not like).

For more information on search conditions, see the "where Clause" section in the Reference Manual.

Comparison operators

Transact-SQL uses these comparison operators:

Comparison operators
Operator	Meaning
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to
!=	Not equal to (Transact-SQL extension)
!>	Not greater than (Transact-SQL extension)
!<	Not less than (Transact-SQL extension)

The operators are used in this syntax:

where expression comparison_operator expression

An expression is a constant, column name, function, subquery, case expression, or any combination of these, connected by arithmetic or bitwise operators. In comparing character data, < means earlier in the sort order and > means later in the sort order. Use the sp_helpsort to display the sort order for your Adaptive Server.

Trailing blanks are ignored for the purposes of comparison. For example, "Dirk" is the same as "Dirk ". In comparing dates, < means earlier than and > means later than. Put apostrophes or quotation marks around all char, nchar, unichar, varchar, nvarchar, univarchar, text, and datetime data. For more information on entering datetime data, see Chapter 8, "Adding, Changing, and Deleting Data."

Here are some sample select statements that use comparison operators:

select * 
from titleauthor 
where royaltyper < 50

select authors.au_lname, authors.au_fname 
from authors 
where au_lname > "McBadden"

select au_id, phone 
from authors 
where phone != "415 658-9932"

select title_id, newprice = price * $1.15 
from  pubs2..titles 
where advance > 5000

not negates an expression. Either of the following two queries finds all business and psychology books that have advances of less than $5500. Note the difference in position between the negative logical operator (not) and the negative comparison operator (!>).

select title_id, type, advance 
from titles 
where (type = "business" or type = "psychology") 
and not advance >5500

select title_id, type, advance 
from titles 
where (type = "business" or type = "psychology") 
and advance !>5500

title_id  type             advance

--------  ------------    -------- 
BU1032    business        5,000.00 
BU1111    business        5,000.00 
BU7832    business        5,000.00 
PS2091    psychology      2,275.00 
PS3333    psychology      2,000.00 
PS7777    psychology      4,000.00 
 
(6 rows affected)

Ranges (between and not between)

Use the between keyword to specify an inclusive range.

For example, to find all the books with sales between and including 4095 and 12,000, you can write this query:

select title_id, total_sales 
from titles 
where total_sales between 4095 and 12000

title_id  total_sales 
------    -----------
BU1032           4095 
BU7832           4095 
PC1035           8780 
PC8888           4095 
TC7777           4095 
 
(5 rows affected)

You can specify an exclusive range with the greater than (>) and less than (<) operators:

select title_id, total_sales 
from titles 
where total_sales > 4095 and total_sales < 12000

title_id  total_sales 
------    -----------
PC1035           8780 
 
(1 row affected)

not between finds all rows outside the specified range. To find all the books with sales outside the $4095 to $12,000 range, type:

select title_id, total_sales 
from titles 
where total_sales not between 4095 and 12000

title_id     total_sales 
--------     -----------
BU1111              3876 
BU2075             18722 
MC2222              2032 
MC3021             22246 
PS1372               375 
PS2091              2045 
PS2106               111 
PS3333              4072 
PS7777              3336 
TC3218               375 
TC4203             15096 
 
(11 rows affected)

Lists (in and not in)

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 values list can be a set of constants or a subquery.

For example, to list the names and states of all authors who live in California, Indiana, or Maryland, you can use:

select au_lname, state 
from authors 
where state = "CA" or state = "IN" or state = "MD"

Or, to get the same results with less typing, use in. Separate items following the in keyword by commas and enclose them in parentheses. Put single or double quotes around char, varchar, unichar, univarchar and datatime values. For example:

select au_lname, state 
from authors 
where state in ("CA", "IN", "MD")

This is what results from either query:

 au_lname         state 
 -----------      ----- 
 White            CA 
 Green            CA 
 Carson           CA 
 O'Leary          CA 
 Straight         CA 
 Bennet           CA 
 Dull             CA 
 Gringlesby       CA 
 Locksley         CA 
 Yokomoto         CA 
 DeFrance         IN 
 Stringer         CA 
 MacFeather       CA 
 Karsen           CA 
 Panteley         MD 
 Hunter           CA 
 McBadden         CA 
 
(17 rows affected)

Perhaps the most important use for the in keyword is in nested queries, also called subqueries. For a full discussion of subqueries, see Chapter 5, "Subqueries: Using Queries Within Other Queries." The following example gives an idea of what you can do with nested queries and the in keyword.

Suppose you want to know the names of the authors who receive less than 50 percent of the total royalties on the books they co-author. The authors table gives author names and the titleauthor table gives royalty information. By putting the two tables together using in, but without listing the two tables in the same from clause, you can extract the information you need. The following query:

Searches the titleauthor table for all au_ids of authors making less than 50 percent of the royalty on any one book.

Selects from the authors table all the author names with au_ids that match the results from the titleauthor query. The results show that several authors fall into the less than 50 percent category.

select au_lname, au_fname 
from authors 
where au_id in 
  (select au_id 
   from titleauthor 
   where royaltyper <50)

au_lname          au_fname 
--------------    ------------ 
Green             Marjorie 
O'Leary           Michael 
Gringlesby        Burt 
Yokomoto          Akiko 
MacFeather        Stearns 
Ringer            Anne 
 
(6 rows affected)

not in finds the authors that do not match the items in the list. The following query finds the names of authors who do not make less than 50 percent of the royalties on at least one book.

select au_lname, au_fname 
from authors 
where au_id not in 
  (select au_id 
   from titleauthor 
   where royaltyper <50)

au_lname          au_fname 
---------------   ------------ 
White             Johnson 
Carson            Cheryl 
Straight          Dick 
Smith             Meander 
Bennet            Abraham 
Dull              Ann 
Locksley          Chastity 
Greene            Morningstar 
Blotchet-Halls    Reginald 
del Castillo      Innes 
DeFrance          Michel 
Stringer          Dirk 
Karsen            Livia 
Panteley          Sylvia 
Hunter            Sheryl 
McBadden          Heather 
Ringer            Albert 
Smith             Gabriella
 
(18 rows affected)


	Expand Search