NOW LIVEEmpower your end users with Explorations in Mode.Try it now

## Mode Studio

The Collaborative Data Science Platform

# SQL NOT

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

## The SQL NOT operator

`NOT` is a logical operator in SQL that you can put before any conditional statement to select rows for which that statement is false.

Here's what `NOT` looks like in action in a query of Billboard Music Charts data:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND year_rank NOT BETWEEN 2 AND 3
``````

In the above case, you can see that results for which `year_rank` is equal to 2 or 3 are not included.

Using `NOT` with `<` and `>` usually doesn't make sense because you can simply use the opposite comparative operator instead. For example, this query will return an error:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND year_rank NOT > 3
``````

Instead, you would just write that as:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND year_rank <= 3
``````

`NOT` is commonly used with `LIKE`. Run this query and check out how Macklemore magically disappears!

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND "group" NOT ILIKE '%macklemore%'
``````

`NOT` is also frequently used to identify non-null rows, but the syntax is somewhat special—you need to include `IS` beforehand. Here's how that looks:

``````SELECT *
FROM tutorial.billboard_top_100_year_end
WHERE year = 2013
AND artist IS NOT NULL
``````

## Practice Problem

Write a query that returns all rows for songs that were on the charts in 2013 and do not contain the letter "a".

Try it out See the answer

Next Lesson

SQL ORDER BY

# Get our weekly data newsletter

Work-related distractions for every data enthusiast.