In this lesson, I'll teach you how to use the IN keyword within a SQL statement.

What is the SQL IN Statement?

There are numerous cases in SQL in which you'll want to check whether a value is contained in a list of other values. The SQL IN keyword is designed to solve this problem.

We can use the IN operator to create a condition that checks to see if a value if included in a list of multiple other values.

Here is the general syntax of the IN operator in SQL:

value In (option1, option2, option3...optionN)

Let's consider an actual example now. Let's suppose you wanted a list of films from our DVD Rental database whose rental price was either $0.99 or $4.99

Here is the SQL query that you would write to return this list of films:

SELECT * FROM film

WHERE rental_rate IN (0.99, 4.99);

If you only wanted to generate a list of the films titles (and drop all of the other titles), then you could replace the * wildcard character with the title column, like this:

SELECT title FROM film

WHERE rental_rate IN (0.99, 4.99);

How To Combine the SQL IN and SQL NOT Keyword

The SQL IN keyword can be combined with the SQL NOT keyword to exclude rows that meet a certain criteria.

Let's turn to our DVD Rental database for another example. There are only three unique values for rental_rate within the DVD Rental database:

• $0.99
• $2.99
• $4.99

Suppose that you had a strong belief that films that rented for $0.99 were of low quality and films that rented for $4.99 were grossly overpriced. You want to exclude all movies that rent for $0.99 or $4.99

We can combine the IN and NOT operators to answer this question using the following SQL query:

SELECT title FROM film

WHERE rental_rate NOT IN (0.99, 4.99);

Note that since we are excluding two values and there are only 3 total values in the entire column, then it would probably be better to answer this question with the following query:

SELECT title FROM film

WHERE rental_rate = 2.99;

If there were more than 3 values in the column (or the number of values in the column was unknown), then our original SQL query would be superior.

Final Thoughts

In this lesson, you learned about the IN keyword in the SQL programming language. In the next section, we will explore the SQL GROUP BY statement, which is one of the most powerful capabilities available to SQL programmers.