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

Practice problem

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_name" 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".