Concepts/Intermediate/

IN and NOT IN

An introduction to the expressions IN and NOT IN.

IN and NOT IN are logical operators which restrict the results returned from a query to either belong or not belong to a specific list of values. The values can be either numeric or string.

If we wanted to know which sales agents had deals for the line of GTX products we could write a query like this:

SELECT DISTINCT sales_agent,
       product
  FROM sales_pipeline
 WHERE product IN ("GTK 500", "GTX Basic", "GTX Plus Basic", "GTX Plus Pro", "GTX Pro")
 ORDER BY sales_agent
siyeh/sql-crm-example-data Run query Copy code

The first several rows of the result would look like:

sales_agent product
Anna Snelling GTX Basic
Anna Snelling GTX Plus Basic
Anna Snelling GTX Plus Pro
Boris Faz GTX Basic
Boris Faz GTX Plus Basic
Boris Faz GTX Plus Pro
Cassey Cress GTX Basic
Cassey Cress GTX Plus Basic

A query to show all the won GTX product deals except for the companies Bioholding, Bioplex, Condax, and Faxquote would be:

SELECT DISTINCT sales_agent,
       product,
       account
  FROM sales_pipeline
 WHERE product IN ("GTX Basic", "GTX Plus Basic", "GTX Plus Pro", "GTX Pro")
       AND deal_Stage = "Won"
       AND account NOT IN ("Bioholding", "Bioplex", "Condax", "Faxquote")
siyeh/sql-crm-example-data Run query Copy code

And the first several rows of the results would look like:

sales_agent product account
Cassey Cress GTX Basic Massive Dynamic
Kami Bicknell GTX Basic Labdrill
Garret Kinder GTX Basic Konex
Markita Hansen GTX Basic Y-corporation
Anna Snelling GTX Basic Scottech
Cassey Cress GTX Basic Initech
Rosalina Dieter GTX Basic Konex
Donn Cantrell GTX Basic Plussunin
Donn Cantrell GTX Basic Xx-zobam
Daniell Hammack GTX Basic Ganjaflex

Exercises (Continued from previous section)

There are two ways to do these exercises. The first is to use the “Try query” links to test your queries without saving them. The second is to create a data.world project and save your queries to it. If you are reading this documentation and completing the exercises as a tutorial, you will need to create your own project to save your work. Details and instructions are in the SQL tutorial which has instructions for setting up your project and links to all the current exercises.

Exercise 31

Write a query against the allergies table to return a list of all the patients who have allergies to mould, grass pollen, tree pollen, or house dust mites and the names of their allergies

You can use the results of your query from exercise 3 to see a list of how all the allergies are written in the table

Try query

Hint

SELECT the columns patient, and description FROM the allergies table WHERE the description is IN the list ("Allergy to mould", "Allergy to grass pollen", "Allergy to tree pollen", "House dust mite allergy").

See solution