This page has 0 threads | Add post

Combining WHERE Criteria

Written by Andy Brown

In this tutorial

Combining Criteria using AND
Choosing Alternatives using OR
Combining AND and OR
The Useful IN Keyword for Lists

The previous parts of this tutorial have all looked at single criteria; it's time to lift this restriction!

Combining Criteria using AND

If you use the keyword AND all of the criteria specified must be true. For example, the following query would show all fruit purchased in 2011:

SELECT

Fruit,

DateBought

FROM

tblFruit

-- show all fruit bought in 2011

WHERE

DateBought >= '2011-01-01' and

DateBought <=>'2011-12-31'

Note that you could also accomplish this using the BETWEEN keyword:

WHERE

DateBought between '2011-01-01' and '2011-12-31'

although the best way to do this would be using an SQL function:

WHERE

year(DateBought) = 2011

Choosing Alternatives using OR

If you use the keyword OR, any of the criteria specified can be true. For example, the following query would show fruit purchases which are either of berries or which were made in 2010:

SELECT

Fruit,

DateBought

FROM

tblFruit

-- show purchases of berries or made in 2010

WHERE

year(DateBought) = 2010 or

Fruit like '%berries'

This would give the following result (I've included an extra column to show why the query would return the result in question):

The purchases of Blueberries and Strawberries qualify for inclusion in the results for both reasons, but still only appear once.

Combining AND and OR

You can combine criteria, although you will probably find it useful to enclose them in brackets to emphasise what you're trying to achieve. The following query shows all purchases either made in 2010 or where between 3 and 5 items were bought at a time:

SELECT

Fruit,

DateBought,

qty

FROM

tblFruit

-- show purchases between 3 and 5 items,

-- or which were made in 2010

WHERE

(qty >= 3 and qty <=5)>or

year(DateBought) = 2010

This would return the following 5 rows:

The query returns 5 rows: all of these satisfy at least one of the two crtieria given.

Note that on this occasion you could omit the brackets and get the same result, but the parentheses make it clearer what's going on.

The Useful IN Keyword for Lists

You can use IN to make your queries more readable, by specifying allowable items in a list. Consider the following messy query criteria:

WHERE

Fruit = 'Apples' or

Fruit = 'Pears' or

Fruit = 'Cherries'

This will gives all purchases of the 3 fruit listed. However, you could also write this as:

WHERE

Fruit in ('Apples', 'Pears', 'Cherries')

You can do this with numerical values too. The following query would show all purchases of fruit made in January, February, April or September:

WHERE

Month(DateBought) in (1,2,4,9)

Previous
tutorial

WHERE criteria (version A)

Previous
part

WHERE Criteria with Text Fields: % and _ Wildcards

Top of page

Next
part

Using Sound in Criteria

Next
tutorial

Calculations and Expressions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

WHERE criteria (version B)

Combining WHERE Criteria

Combining Criteria using AND

Choosing Alternatives using OR

Combining AND and OR

The Useful IN Keyword for Lists

Head office

London

Manchester