This page has 0 threads | Add post

Using AND, OR and NOT in SQL Server Queries

Written by Andrew Gould

In this tutorial

Using AND to Combine Criteria
Using OR to Combine Criteria
Using Parentheses to Group Criteria
Using NOT to Exclude Records
What's Next?

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:

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:

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:

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:

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)

--short films with die in name

(FilmName LIKE '%die%' AND

FilmRunTimeMinutes < 120)

The results of this cheery query are shown below:

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.

Previous
tutorial

Writing queries

Previous
part

Date Criteria in SQL Server

Top of page

Next
part

Using NULL in Criteria

Next
tutorial

WHERE criteria (version B)

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

WHERE criteria (version A)

Using AND, OR and NOT in SQL Server Queries

Using AND to Combine Criteria

Using OR to Combine Criteria

Using Parentheses to Group Criteria

Using NOT to Exclude Records

What's Next?

Head office

London

Manchester