564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
The previous parts of this blog have all looked at single criteria; it's time to lift this restriction!
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:
-- show all fruit bought in 2011
DateBought >= '2011-01-01' and
Note that you could also accomplish this using the BETWEEN keyword:
DateBought between '2011-01-01' and '2011-12-31'
although the best way to do this would be using an SQL function:
year(DateBought) = 2011
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:
-- show purchases of berries or made in 2010
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.
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:
-- show purchases between 3 and 5 items,
-- or which were made in 2010
(qty >= 3 and qty <=5)>=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.
You can use IN to make your queries more readable, by specifying allowable items in a list. Consider the following messy query criteria:
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:
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:
Month(DateBought) in (1,2,4,9)
|Parts of this blog|
25 Aytoun Street