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

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

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 outSee the answer

  SELECT *   FROM tutorial.billboard_top_100_year_end   WHERE song_name NOT ILIKE '%a%'  AND year = 2013