Modules

So, you know when you're using SQL and you've got this list of specific things you're looking for in your data?🤔

That's where the IN operator comes in super handy. It's like telling SQL, "Hey, can you find me stuff that matches any of these things in my list?"

For instance, let's say you're digging through a music chart database. You want to find songs that are either 1st, 2nd, or 3rd in the year's rankings. Here's how you'd ask SQL for that:

SELECT *
FROM music_chart
WHERE year_rank IN (1, 2, 3);

Pretty cool, right? SQL will then show you only the songs that hit those top 3 spots.

And hey, it's not just about numbers. You can look for text too. Just remember to wrap your words in single quotes. So, if you're curious about tracks by Taylor Swift, Usher, or Ludacris, you'd do it like this:

SELECT *
FROM music_chart
WHERE artist IN ('Taylor Swift', 'Usher', 'Ludacris');

Just like that, SQL will fetch the songs by these artists. The key thing is to separate your list items with commas, whether they're numbers or text.

What is the difference with NOT IN? You probably guess it! 😄

Think of NOT IN as the opposite of IN. It's like telling SQL to fetch stuff that does not match the things in your list. So, imagine you're still in that music chart database, but now you want to see songs that aren't from Taylor Swift (you heard her so much on Spotify and everyone keeps talking about her on social media). Also, you don't wanna hear any songs from Eminem (sorry bud - it was random). You will just need to do that:

SELECT *
FROM music_chart
WHERE artist NOT IN ('Taylor Swift', 'Eminem');

But here's a little twist: NOT IN can get a bit tricky when dealing with null values. You see, in SQL, null is like a wild card; it's not empty, but it's not something specific either. It's just... undefined. So imagine having an artist that's NULL on the Data, this is definitely not Taylor Swift or Eminem, still, it won't account for it.

To account for null artist names you will need to do this:

SELECT *
FROM music_chart
WHERE artist NOT IN ('Taylor Swift', 'Eminem') OR artist IS NULL;

We can also do something like this:

SELECT *
FROM music_chart
WHERE COALESCE(artist, 'no name') NOT IN ('Taylor Swift', 'Eminem')

COALESCE is like IFNULL in Excel, if the value is null it will replace it with a value called "no name".

Awesome work! Now let's practice!