mode helix
NEWIntroducing Helix—the first instant, responsive data engine.Learn more

SQL NOT

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

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

Sharpen your SQL skills

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

Looks like you've got a thing for cutting-edge data news.

Get the latest.

Get started