SQLite IN

You organize your data. Now organize your savings.

Set savings goals, track your progress, and stay on course.

Get Savigo for iPhone ›

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 #

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);Copy

The 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.

The 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.

SQLite 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.

SELECT
	TrackId,
	Name,
	Mediatypeid
FROM
	Tracks
WHERE
	MediaTypeId IN (1, 2)
ORDER BY
	Name ASC;Copy

Try It

This query uses the OR operator instead of the IN operator to return the same result set as the above query:

SELECT
	TrackId,
	Name,
	MediaTypeId
FROM
	Tracks
WHERE
	MediaTypeId = 1 OR MediaTypeId = 2
ORDER BY
	Name ASC;Copy

Try It

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.

SQLite IN operator with a subquery example #

The following query returns a list of album id of the artist id 12:

SELECT albumid
FROM albums
WHERE artistid = 12;Copy

Try It

To get the tracks that belong to the artist id 12, you can combine the IN operator with a subquery as follows:

SELECT
	TrackId, 
	Name, 
	AlbumId
FROM
	Tracks
WHERE
	AlbumId IN (
		SELECT
			AlbumId
		FROM
			Albums
		WHERE
			ArtistId = 12
	);Copy

Try It

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).

SELECT
	trackid,
	name,
	genreid
FROM
	tracks
WHERE
	genreid NOT IN (1, 2,3);Copy

Try It

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.