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
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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.
|
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:
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
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.
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)>=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:
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)
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.