BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- The WHERE Clause in SQL to filter records using criteria
- WHERE Criteria with Text Fields: % and _ Wildcards
- Combining WHERE Criteria (this blog)
- 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):
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)
- The WHERE Clause in SQL to filter records using criteria
- WHERE Criteria with Text Fields: % and _ Wildcards
- Combining WHERE Criteria (this blog)
- Using Sound in Criteria