Setting SQL criteria using the WHERE clause
Part three of a four-part series of blogs

If you don't want to see a full set of records from a table in SQL, you can use a WHERE statement to filter out unwanted rows, as shown in this blog.

  1. The WHERE Clause in SQL to filter records using criteria
  2. WHERE Criteria with Text Fields: % and _ Wildcards
  3. Combining WHERE Criteria (this blog)
  4. Using Sound in Criteria

This blog is part of our online SQL tutorial blog.  If you want to learn SQL in a classroom environment, have a look at our SQL training courses.

Posted by Andy Brown on 08 November 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Combining WHERE Criteria

The previous parts of this blog 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):

4 rows returned from query

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 5 rows returned

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)

This blog has 0 threads Add post