{"id":596,"date":"2015-12-19T22:08:08","date_gmt":"2015-12-19T15:08:08","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=596"},"modified":"2022-04-03T14:58:15","modified_gmt":"2022-04-03T07:58:15","slug":"sqlite-in","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-in\/","title":{"rendered":"SQLite IN"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite IN<\/code> operator to determine whether a value matches any value in a list of values or a result of a subquery.<\/p>\r\n\r\n\r\n\r\n

Introduction to the SQLite IN operator<\/h2>\r\n\r\n\r\n\r\n

The SQLite IN<\/code> operator determines whether a value matches any value in a list or a subquery<\/a>. The syntax of the IN <\/code>operator is as follows:<\/p>\r\n\r\n\r\n

expression [NOT] IN (value_list|subquery);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The expression<\/code> can be any valid expression or a column of a table.<\/p>\r\n\r\n\r\n\r\n
The IN<\/code> 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<\/code> operator.<\/p>\r\n\r\n\r\n\r\n
SQLite IN operator examples<\/h2>\r\n\r\n\r\n\r\n
We will use the Tracks<\/code> table from the sample database<\/a> for the demonstration.<\/p>\r\n\r\n\r\n\r\n
\"\"<\/figure>\r\n\r\n\r\n\r\n
The following statement uses the IN<\/code> operator to query the tracks whose media type id is 1 or 2.<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tTrackId,\r\n\tName<\/span>,\r\n\tMediatypeid\r\nFROM<\/span>\r\n\tTracks\r\nWHERE<\/span>\r\n\tMediaTypeId IN<\/span> (1<\/span>, 2<\/span>)\r\nORDER<\/span> BY<\/span>\r\n\tName<\/span> ASC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
This query uses the OR<\/code> operator instead of the IN<\/code> operator to return the same result set as the above query:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tTrackId,\r\n\tName<\/span>,\r\n\tMediaTypeId\r\nFROM<\/span>\r\n\tTracks\r\nWHERE<\/span>\r\n\tMediaTypeId = 1<\/span> OR<\/span> MediaTypeId = 2<\/span>\r\nORDER<\/span> BY<\/span>\r\n\tName<\/span> ASC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
As you can see from the queries, using the IN<\/code> operator is much shorter.<\/p>\r\n\r\n\r\n\r\n
If you have a query that uses many OR<\/code> operators, you can consider using the IN<\/code> operator instead to make the query more readable.<\/p>\r\n\r\n\r\n\r\n
SQLite IN operator with a subquery example<\/h3>\r\n\r\n\r\n\r\n
The following query returns a list of album id of the artist id 12:<\/p>\r\n\r\n\r\n
SELECT<\/span> albumid\r\nFROM<\/span> albums\r\nWHERE<\/span> artistid = 12<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
To get the tracks that belong to the artist id 12, you can combine the IN<\/code> operator with a subquery<\/a> as follows:<\/p>\r\n\r\n\r\n
SELECT<\/span>\r\n\tTrackId, \r\n\tName<\/span>, \r\n\tAlbumId\r\nFROM<\/span>\r\n\tTracks\r\nWHERE<\/span>\r\n\tAlbumId IN<\/span> (\r\n\t\tSELECT<\/span>\r\n\t\t\tAlbumId\r\n\t\tFROM<\/span>\r\n\t\t\tAlbums\r\n\t\tWHERE<\/span>\r\n\t\t\tArtistId = 12<\/span>\r\n\t);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
\"SQLite<\/figure>\r\n\r\n\r\n\r\n
In this example:<\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
Try It<\/a><\/p>\r\n\r\n\r\n\r\n
A list of values is a fixed value list or a result set of a single column returned by a subquery. The returned type<\/a> of expression and values in the list must be the same.<\/p>\r\n\r\n\r\n\r\n