Checking Multiple Conditions

Contact Us or call 1-877-932-8228
Checking Multiple Conditions

Checking Multiple Conditions

AND

AND can be used in a WHERE clause to find records that match more than one condition.

Code Sample:

SimpleSelects/Demos/Where-And.sql
/*
Create a report showing the first and last name of all
sales representatives whose title of courtesy is "Mr.".
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
	AND TitleOfCourtesy = 'Mr.';

The above SELECT statement will return the following results:And Results

OR

OR can be used in a WHERE clause to find records that match at least one of several conditions.

Code Sample:

SimpleSelects/Demos/Where-Or.sql
/*
	Create a report showing the first and last name and the city of all 
	employees who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond';

The above SELECT statement will return the following results:Or Results

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

Code Sample:

SimpleSelects/Demos/Where-AndOrPrecedence.sql
/*
	Create a report showing the first and last name of all sales 
	representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond'
	AND Title = 'Sales Representative';

The above SELECT statement will return the following results:Bad Precedence Results

Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond.

This can be fixed by putting the OR portion of the clause in parentheses.

Code Sample:

SimpleSelects/Demos/Where-AndOrPrecedence2.sql
/*
	Create a report showing the first and last name of all sales 
	representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE (City = 'Seattle' OR City = 'Redmond')
	AND Title = 'Sales Representative';

The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the same results minus Laura Callahan.Good Precedence Results

If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.

Next