Big SQL Energy Beginner Course: Lesson 11 IN & NOT IN

Jan 12, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 11 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning about IN & NOT IN in SQL!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 11: IN & NOT IN

🎥 Video

We already know how to search for multiple conditions combined with Boolean operators in SQL like this:

select 
   *
 from 
    orders
 where 
    product_name = 'Laptop'
    or product_name = 'Monitor'
    or product_name = 'Mouse'

This query will pull all of the rows where the product name is either Laptop, Monitor, or Mouse. It’s a perfectly fine solution, but it does have redundant code since it repeats the “product_name =” part multiple times. Sometimes, we’re searching for many, many items, and it’s harder to manually repeat this logic over and over. A way to simplify the code and search for each of these items is by using the IN operator and putting all of the values in a list. That way, SQL will check each product_name against the list instead repeating the logic over and over for each value. Notice how we can check product_name in the list of strings below:

select 
   *
 from 
    orders
 where 
    product_name in ('Laptop', 'Monitor', 'Mouse')

And of course, we can also negate the condition with NOT to keep ONLY the rows that don’t meet that condition (i.e. don’t equal any of the values in the provided list).

select 
   *
 from 
    orders
 where 
    product_name not in ('Laptop', 'Monitor', 'Mouse')

The 2 examples above are looking for string values in the product_name column, but we can also look for non-string values as well. See the example below where we filter for a list of numeric values instead:

select 
   *
 from 
    orders
 where 
    customer_id in (1, 2, 3, 4)

There are also advanced use cases of IN where we can search for values using a SUBQUERY. I know subqueries are one of the most dreaded topics to learn because they can be a tad confusing, but don’t worry about them too much yet if you’re a newbie. I have a whole chapter about subqueries in my 🔗 intermediate course once you get there, and I make them super easy to understand! Check out the example below just to see how we can use IN to filter for the results of a subquery.

select 
   *
 from 
    orders
 where 
    customer_id in (select distinct id from customers)

A subquery is a query nested inside of another query. Here we’re filtering the main query based on the results from the subquery. Head on over to my 🔗 intermediate course to learn more about these!

IN and NOT IN are amazing tools to have to filter data for large amounts of values using lists. If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!