BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
When you write queries in SQL it's immensely useful to be able to show records matching criteria that you've set. You can do this using the WHERE clause and this blog teaches you how to use it!
- Using Criteria in SQL Queries (this blog)
- Criteria Using Numbers
- Criteria Using Text
- Date Criteria in SQL Server
- Using AND, OR and NOT in SQL Server Queries
- Using NULL in Criteria
This blog is part of our online SQL tutorial blog - however, Wise Owl also run SQL courses for up to 6 people.
Posted by Andrew Gould on 16 January 2013
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.
Using Criteria in SQL Queries
This blog teaches you how to add criteria to your SQL queries to find specific records in a table. You'll learn about how to work with text, dates and numbers in the WHERE clause of a query.
The WHERE Clause
If you've read our blog on basic SQL queries you'll remember that the WHERE clause is the third of six keywords in a basic query. If you didn't read that blog or you just need a reminder here's where it fits in:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
The Structure of Basic Criteria
The most basic criteria you can write in SQL queries involve comparing the value of a field against another value that you provide. In the example below we're comparing the value of the FilmRunTimeMinutes field with the value 120 and asking if they are equal to each other:
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
WHERE
FilmRunTimeMinutes = 120
When this query is executed we'll return all of the films whose running time is equal to the number we have provided:
These films all last for 120 minutes.
Should You Use Brackets to Contain Criteria?
You might sometimes see people writing their criteria contained within parentheses, as shown below:
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
WHERE
(FilmRunTimeMinutes = 120)
With such a simple query the brackets make no difference to the results and so you can happily leave them out.
For more complex combinations of criteria you may find brackets helpful to clarify your query for other readers. A later part of this blog series shows an example where using brackets is essential for SQL Server to understand what you're trying to do!
What's Next?
Now that you know the basics of where the WHERE clause fits and what it's for, it's time to look in more detail at criteria involving specific data types; starting with numbers.
- Using Criteria in SQL Queries (this blog)
- Criteria Using Numbers
- Criteria Using Text
- Date Criteria in SQL Server
- Using AND, OR and NOT in SQL Server Queries
- Using NULL in Criteria