![]() 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. ![]() Genreid NOT IN ( 1, 2, 3) Code language: SQL (Structured Query Language) ( sql ) ![]() The following statement returns a list of tracks whose genre id is not in a list of (1,2,3). Then, the outer query return all tracks whose album id matches with the album id list returned by the subquery.First, the subquery returns a list of album ids that belong to the artist id 12.) Code language: SQL (Structured Query Language) ( sql ) To get the tracks that belong to the artist id 12, you can combine the IN operator with a subquery as follows: SELECT WHERE artistid = 12 Code language: SQL (Structured Query Language) ( sql ) The following query returns a list of album id of the artist id 12: SELECT albumid SQLite IN operator with a subquery example If you have a query that uses many OR operators, you can consider using the IN operator instead to make the query more readable. MediaTypeId = 1 OR MediaTypeId = 2 ORDER BY Name ASC Code language: SQL (Structured Query Language) ( sql )Īs you can see from the queries, using the IN operator is much shorter. This query uses the OR operator instead of the IN operator to return the same result set as the above query: SELECT ORDER BY Name ASC Code language: SQL (Structured Query Language) ( sql ) The following statement uses the IN operator to query the tracks whose media type id is 1 or 2. We will use the Tracks table from the sample database for the demonstration. To negate the list of values, you use the NOT IN operator. First, we need to create a new database and open a database connection to allow sqlite3 to work with it. It assumes a fundamental understanding of database concepts, including cursors and transactions. As an aside: the cross-platform GUI application DB browser for SQLite (whose executable and package name for Linux is sqlitebrowser) is great for fast database exploration. In this tutorial, you will create a database of Monty Python movies using basic sqlite3 functionality. The IN operator returns true or false depending on whether the expression matches any value in a list of values or not. It’s all about reading and formatting data. The returned type of expression and values in the list must be the same. The expression can be any valid expression or a column of a table.Ī list of values is a fixed value list or a result set of a single column returned by a subquery. The syntax of the IN operator is as follows: expression IN (value_list|subquery) Code language: SQL (Structured Query Language) ( sql ) The SQLite IN operator determines whether a value matches any value in a list or a subquery. The SQLite project delivers a simple command-line tool named sqlite3 (or sqlite3.exe on Windows) that allows you to interact with the SQLite databases using SQL statements and commands. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |