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
- Criteria Using Numbers (this blog)
- 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.

# Criteria Using Numbers

Numbers are perhaps the easiest type of data for which to write criteria, which is why we're using them as a starting point.

## Comparison Operators for Numbers

You can use a range of different symbols, known as *operators*, for
comparing numbers: the table below shows you the main ones you're likely
to use:

Operator | Alternative | What it means |
---|---|---|

= | Equal to | |

<> | != | Not equal to |

> | Greater than | |

< | Less than | |

>= | !< | Greater than or equal to (or not less than) |

<= | !> | Less than or equal to (or not greater than) |

The operator always sits between the two values you are comparing. For
example, the query below compares the value of the** FilmRunTimeMinutes**
field with the value
**180**:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes >= 180

The query shows all of the films whose running time is greater than or equal to 180:

The shortest film in this list is 180 minutes long.

## Finding Numbers Within a Range

You can use the **BETWEEN** keyword to find numbers that fall
between an upper and lower limit. The example below would find all of the
films whose running time is between **90** and **100**
minutes.

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes BETWEEN 90 AND 100

A selection of the results from this query are shown below:

You can see from the diagram that films lasting exactly
**90** and **100** minutes are included in the results of the query.

## Finding Numbers in a List of Values

You can use the **IN** keyword to find records that match any
values in a list of numbers. The example below looks for films whose
running time is exactly **100**, **150** or **
200** minutes:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes IN (100,150,200)

Note that the list of numbers must be enclosed in a set of parentheses. The results of this query are shown in the diagram below:

There aren't any films with a running time of exactly
**200** minutes.

## What's Next?

Now that you've seen how easy it is to write criteria using numbers it's time to see a few of the quirks of writing criteria involving text.

- Using Criteria in SQL Queries
- Criteria Using Numbers (this blog)
- Criteria Using Text
- Date Criteria in SQL Server
- Using AND, OR and NOT in SQL Server Queries
- Using NULL in Criteria