SQL can sometimes look a little intimidating. But don’t worry! Today, we’re going to dive into a super handy feature: filtering with lists using the IN keyword. It’s easy to learn and can save you loads of time. Let’s break it down together and have a little fun along the way!
TL;DR
The SQL IN keyword lets you check if a value matches any value in a list. It’s like asking, “Is this in my shopping bag?” It helps simplify your queries, especially when filtering by multiple values. Think of it as a smarter alternative to writing a bunch of ORs.
What is SQL IN?
SQL IN is like a bouncer at a party. It checks if something is on the guest list. If it is, it gets in. If not, it’s out.
Let’s look at a basic example. Suppose you have a table called Students and you want to find students from three specific cities. You could write:
SELECT * FROM Students
WHERE City = 'New York'
OR City = 'Los Angeles'
OR City = 'Chicago';
But that’s kind of long, right? There’s a better way:
SELECT * FROM Students
WHERE City IN ('New York', 'Los Angeles', 'Chicago');
Much cleaner. Much easier.
How Does IN Work?
The list inside the parentheses is what we compare against. The column on the left (like City) is checked against ALL values in the list.
If a student’s city matches any of them, they’re included in the results.
That’s it. Simple, quick, and powerful.
When Should I Use It?
Use IN whenever:
- You want to match more than one value.
- Your list can be small or huge — it doesn’t matter.
- You’re tired of typing
ORover and over.
Here are some examples:
- Filter employees by department IDs
- Select orders from several countries
- Find products by a set of known prices
Let’s Get Real With Examples
Imagine you work at an online bookstore and your table is called Books.
SELECT * FROM Books
WHERE Genre IN ('Science Fiction', 'Fantasy', 'Mystery');
This query brings back only your favorites. Just by using a list. Easy!
It Works With Numbers Too
This isn’t just for text. Numbers love IN too! 🎉
SELECT * FROM Orders
WHERE CustomerID IN (101, 103, 110, 121);
You just pulled orders from four customers. Boom.
What About NOT IN?
You’re feeling rebellious today? Try NOT IN.
SELECT * FROM Employees
WHERE Role NOT IN ('Intern', 'Contractor');
This gives you everyone except interns and contractors. It’s like saying, “Give me everyone who’s not on this list!”
Tip: Use NOT IN carefully with NULL values. Sometimes, it can act weird.
Using IN with Subqueries
Here’s where things get spicy 🌶️. You can use another query inside your list!
SELECT * FROM Products
WHERE ProductID IN (
SELECT ProductID FROM Orders
WHERE CustomerID = 105
);
Whoa! You’re using results from one query to filter another. Like magic ✨.
This way, you don’t have to know the IDs ahead of time. Let SQL do the thinking.
Some Fun Analogies
- Shopping: Only put the fruits you like in the basket. Bananas, apples, oranges? Write them in an
INlist! - Invites: Hosting a party? Only invite people IN this list: Alice, Bob, Charlie.
- Music Playlist: Want to play songs only from specific artists? Your playlist can use
INto filter just those!
Case Sensitivity Tips
In most systems, text is case-insensitive. But some databases (like PostgreSQL) pay attention to upper and lowercase letters.
If you’re not getting results, check the case. Or use functions like UPPER() or LOWER() to help match better.
SELECT * FROM People
WHERE UPPER(City) IN ('LONDON', 'PARIS', 'TOKYO');
Don’t Do This 🚫
Please, for the love of SQL, try not to do this:
SELECT * FROM Sales
WHERE Region = 'West' OR Region = 'East' OR Region = 'North' OR Region = 'South';
This is a perfect job for IN!
SELECT * FROM Sales
WHERE Region IN ('West', 'East', 'North', 'South');
Cleaner queries = happier developers 😊
Bonus: Use With JOINs
Sometimes, you pair IN with JOIN when you don’t want to pull in full rows from another table but still want filtering.
SELECT * FROM Customers
WHERE CustomerID IN (
SELECT CustomerID FROM VIPList
);
This avoids a full join but still filters your results smartly.
Final Thoughts
The SQL IN keyword is one of your best friends. It helps you:
- Write shorter, clearer queries
- Search through lists easily
- Avoid copy-pasting a thousand ORs
- Use subqueries like a pro
Keep it simple. Keep it readable. Use IN.
Practice Time!
Want to improve quickly? Try these:
- List employees in (‘Accounting’, ‘HR’, ‘IT’)
- Find orders from customers #112, #115, or #120
- Exclude products that are in (‘Discontinued’, ‘Out of Stock’)
Write the queries. Run them on your test data. You learn by doing!
That’s a Wrap!
Now you know how IN makes your SQL life better. Think of it as your list-loving friend who just wants your code to look nice and work fast. Go on and show off your clean, readable queries.
Happy SQL-ing! 🍕
