Summary: in this tutorial, you will learn how to use the SQLite
IN operator to determine whether a value matches any value in a list of values or a result of a subquery.
Introduction to the SQLite
IN operator determines whether a value matches any value in a list or a subquery. The syntax of the
IN operator is as follows:
expression [NOT] IN (value_list|subquery);
expression can be any valid expression or a column of a table.
A list of values is a fixed value list or a result set of a single column returned by a subquery. The returned type of expression and values in the list must be the same.
IN operator returns true or false depending on whether the expression matches any value in a list of values or not. To negate the list of values, you use the
NOT IN operator.
IN operator examples
We will use the
Tracks table from the sample database for the demonstration.
The following statement uses the
IN operator to query the tracks whose media type id is 1 or 2.
MediaTypeId IN (1, 2)
This query uses the
OR operator instead of the
IN operator to return the same result set as the above query:
MediaTypeId = 1 OR MediaTypeId = 2
As you can see from the queries, using the
IN operator is much shorter.
If you have a query that uses many
OR operators, you can consider using the
IN operator instead to make the query more readable.
IN operator with a subquery example
The following query returns a list of album id of the artist id 12:
WHERE artistid = 12;
To get the tracks that belong to the artist id 12, you can combine the
IN operator with a subquery as follows:
AlbumId IN (
ArtistId = 12
In this example:
- First, the subquery returns a list of album ids that belong to the artist id 12.
- Then, the outer query return all tracks whose album id matches with the album id list returned by the subquery.
SQLite NOT IN examples
The following statement returns a list of tracks whose genre id is not in a list of (1,2,3).
genreid NOT IN (1, 2,3);
In this tutorial, you have learned how to use the SQLite
IN operator to match a value with a list of values or a subquery.