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

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")

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