Home

	Expand Search

Transact-SQL User's Guide

Chapter 2 Queries: Selecting Data from a Table

Pattern matching

Connecting conditions with logical operators

Connecting conditions with logical operators

The logical operators and, or, and not are used to connect search conditions in where clauses. The syntax is:

{where | having} [not] 
     column_name join_operator column_name 

where join_operator is a comparison operator and column_name is the column used in the comparison. Qualify the name of the column if there is any ambiguity.

and 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 author's last name is Ringer and the author's first name is Anne. It does not find the row for Albert Ringer.

select * 
from authors 
where au_lname = "Ringer" and au_fname = "Anne" 

or also connects two or more conditions, but it returns results when any of the conditions is true. The following query searches for rows containing Anne or Ann in the au_fname column.

select * 
from authors 
where au_fname = "Anne" or au_fname = "Ann" 

You can specify as many as 252 and and or conditions.

not negates the expression that follows it. The following query selects all the authors who do not live in California:

select * from authors 
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 authors 
where (city = "Oakland" or city = "Berkeley") and state = "CA" 

Arithmetic and bitwise operators are handled before logical operators. When more than one logical operator is used in a statement, not is evaluated first, then and, and finally or. See "Bitwise operators" for more information.

For example, the following query finds all the business books in the titles table, no matter what their advances are, as well as all psychology books that have an advance of more than $5500. The advance condition pertains only to psychology books because the and is handled before the or.

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 
BU2075    business    10,125.00 
BU7832    business     5,000.00 
PS1372    psychology   7,000.00 
PS2106    psychology   6,000.00 
 
(6 rows affected) 

You can change the meaning of the query by adding parentheses to force evaluation of the or first. This query finds all business and psychology books that have advances of more than $5500:

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

title_id  type         advance 
--------  ----------   --------- 
BU2075    business     10,125.00 
PS1372    psychology    7,000.00 
PS2106    psychology    6,000.00 
 
(3 rows affected)