Using Criteria in SQL Queries
Part five of a six-part series of blogs

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!

  1. Using Criteria in SQL Queries
  2. Criteria Using Numbers
  3. Criteria Using Text
  4. Date Criteria in SQL Server
  5. Using AND, OR and NOT in SQL Server Queries (this blog)
  6. 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 AND, OR and NOT in SQL Server Queries

All of the queries we've created in this tutorial so far have each used a single criterion.  In this part of the series we're going to look at how to combine criteria using the AND and OR logical operators.  We'll also cover how to use the NOT operator to exclude records from the results of a query.

Using AND to Combine Criteria

When you want to create a list of criteria which must all be met you can use the AND keyword:

SELECT

FilmName

,FilmReleaseDate

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName LIKE '%star%' AND

FilmReleaseDate > '1979-12-31' AND

FilmRunTimeMinutes > 120

In order for a film to be displayed in the results its name must contain the word star, its release date must be after 1979 and its running time must be greater than 120.  The results of this query are shown below:

Results of AND

Each of these films match all three of the specified criteria.

Using OR to Combine Criteria

You can also join multiple criteria using the OR keyword.  When you do this each record in the results only has to match a single one of the criteria in your list:

SELECT

FilmName

,FilmReleaseDate

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName LIKE '%star%' OR

FilmReleaseDate > '1979-12-31' OR

FilmRunTimeMinutes > 120

In this example a film will show up in the results if it has the word star in its name, or if it was released after 1979, or if its running time is longer than 120 minutes.  This means that we get a lot more records in our results:

Results of OR

Each of these films matches at least one of the specified criteria.

Using Parentheses to Group Criteria

You can use a combination of AND and OR keywords in the same query.  When you do this however, you have to be careful about the way SQL Server groups your criteria together. 

In the example below I'd like to see any films that are longer than 120 minutes and which have the words star or war in their name:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName LIKE '%star%' OR

FilmName LIKE '%war%' AND

FilmRunTimeMinutes > 120

This should mean that a film will only be returned if its run time is greater than 120 minutes.  Unfortunately, I don't get quite the results I want:

Incorrect results

Some of the films which contain the word star are shorter than 120 minutes.

 

What SQL Server is returning is any film whose name contains the word star, as well as films that are longer than 120 minutes and which have the word war in their name.  In essence, the first criterion is treated independently while the second two are grouped together.

We can override this behaviour by using parentheses (or brackets):

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

(FilmName LIKE '%star%' OR

FilmName LIKE '%war%') AND

FilmRunTimeMinutes > 120

Adding a set of parentheses around the first two criteria will group them together and affect the results of the query:

Correct results

This time we only see films which are longer than 120 minutes.

 

You can use multiple sets of parentheses in the same query to ensure that your criteria are evaluated in the way you want.  The example below groups all of the criteria from the query shown above and adds another pair of criteria:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

--long films with star or war in name

((FilmName LIKE '%star%' OR

FilmName LIKE '%war%') AND

FilmRunTimeMinutes > 120)

OR

--short films with die in name

(FilmName LIKE '%die%' AND

FilmRunTimeMinutes < 120)

The results of this cheery query are shown below:

Multiple parentheses

Judicious use of comments helps to remind you what you're trying to do!

 

Using NOT to Exclude Records

We've already talked about how to write criteria asking where a field is not equal to a specific value, for example:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName <> 'Die Hard'

But what if you wanted to exclude all films which contain the words Die Hard?  Unfortunately, this technique will not work:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName <> '%Die Hard%'

Instead you must use the LIKE keyword in combination with the NOT operator:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmName NOT LIKE '%Die Hard%'

You can also use NOT in combination with IN, as shown below:

SELECT

FilmName

,FilmRunTimeMinutes

FROM

tblFilm

WHERE

FilmRunTimeMinutes NOT IN (90,120,150,180)

This query would show all films except those whose run times are exactly 90, 120, 150 or 180 minutes.

What's Next?

In the next part of this tutorial we're going to explain how to find values that aren't there using NULL.

This blog has 0 threads Add post