In this video, you'll learn what the NOT IN keyword is, how to use it with some examples,
and one thing you need to be aware of to ensure you get the right data in your query.
This keyword exists in many different vendors of SQL,
and it's a handy keyword for filtering to get the right data
Let's get into the video.
So what is the NOT IN keyword?
It's a keyword that lets you define which records should be excluded from your query
You add the NOT IN keyword to your where clause, and specify values along with it
It's similar to the IN keyword, but adding the NOT keyword in front of it makes it negative
Let's take a look at how to write one
The first step is to add a WHERE clause to your query
The NOT IN goes in the WHERE clause
We'll start with this simple example here. We're selecting a few fields from the book table.
The NOT IN can be used on Select, Update, Delete, but this example just shows it in a Select query.
Currently there is no WHERE clause, so let's add one.
Here's the query with the WHERE keyword
There's no filtering happening yet. What do we want to filter on?
Let's say we want to show books where the language is not English
After the WHERE clause, we add the column we want to filter on. In this case, it's book_language
So, the second step is to add the column
Add the book_language column to the WHERE clause
Now we've got part of the WHERE clause. We know what column we are filtering on.
The next step is to add the NOT IN keyword. This is written as two separate words:
NOT and IN. There's a space in between.
This query will look for books where the language is NOT IN something.
We need to specify the rest of the query
Step 4 is to add an open bracket. We specify
the values to be excluded from our query inside the brackets, but we need brackets.
So, enter an open bracket
Sometimes your IDE will automatically add a closing bracket as well.
If so, that's great, because we will need that. If not, you can add it now or later.
Step 5 is to add your values. We add in the values here that we want to be excluded from the query.
So any value we mention here will mean that
any book with this language value will be excluded from the results.
In this example, we know that the book language for English is "en",
so we can add that to our query.
The good thing about the NOT IN keyword is we can add multiple values without adding
too much extra code. So, if I wanted to exclude other languages, I could.
Let's say there was another code for English that covered UK English books, which was "en-uk".
I could add that code into the NOT IN list, just by adding a comma and then the value.
The query will now exclude records where the book language is either of those two values.
It's the equivalent of a Not Equals condition in the WHERE clause
Once you've added all of your values, enter a closing bracket.
Your IDE or editor may have already added one.
If so, you don't need to add another one. It just needs to match the start of the NOT IN keyword.
Now your query is ready!
Run the query and see your results
You should see results that match the criteria entered.
In our example, we would see books where the language is not equal to en or en-uk.
And that's how easy the NOT IN keyword is to use!
There's one thing you need to be aware of when working with NOT IN,
and that's how the NULL values are treated
If any NULL values are specified inside your NOT IN keyword,
then the result of the NOT IN is false and no records will be returned.
But how can you get a NULL value? This can happen
because you can use a subquery inside the NOT IN keyword.
If a subquery inside NOT IN returns a NULL value, your overall query won't show anything.
So, that's just something to keep in mind.
To get around this, you can build in a WHERE IS NOT NULL keyword into your subquery,
or some other check, to ensure there are no NULL values found.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit
[databasestar.com](http://databasestar.com/).
That’s where I share my best database-related content.
Which step from this tutorial was the most helpful for you? Was it the fact that it was so easy to
add multiple values, or that NULL values are something to be aware of, or something else?
Thanks for watching.