{"id":157,"date":"2015-11-30T16:00:55","date_gmt":"2015-11-30T09:00:55","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=157"},"modified":"2024-04-12T06:24:38","modified_gmt":"2024-04-11T23:24:38","slug":"sqlite-where","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-where\/","title":{"rendered":"SQLite Where"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use SQLite WHERE<\/code> clause to specify the search condition for rows returned by the query.<\/p>\n\n\n\n

Introduction to SQLite WHERE clause<\/h2>\n\n\n\n

The WHERE<\/code> clause is an optional clause of the SELECT<\/a><\/code> statement. It appears after the FROM<\/code> clause as the following statement:<\/p>\n\n\n

SELECT<\/span>\n  column_list\nFROM<\/span>\n  TABLE<\/span>\nWHERE<\/span>\n  search_condition;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, you add a WHERE<\/code> clause to the SELECT<\/code> statement to filter rows returned by the query. When evaluating a SELECT<\/code> statement with a WHERE<\/code> clause, SQLite uses the following steps:<\/p>\n\n\n\n
    \n
  1. First, check the table in the FROM<\/code> clause.<\/li>\n\n\n\n
  2. Second, evaluate the conditions in the WHERE<\/code> clause to get the rows that met these conditions.<\/li>\n\n\n\n
  3. Third, make the final result set based on the rows in the previous step with columns in the SELECT<\/code> clause.<\/li>\n<\/ol>\n\n\n\n
    The search condition in the WHERE<\/code> has the following form:<\/p>\n\n\n
    left_expression COMPARISON_OPERATOR right_expression<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, you can form a search condition as follows:<\/p>\n\n\n
    WHERE column_1 = 100;\n\nWHERE column_2 IN (1,2,3);\n\nWHERE column_3 LIKE 'An%';\n\nWHERE column_4 BETWEEN 10 AND 20;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    SQLite comparison operators<\/h3>\n\n\n\n
    A comparison operator tests if two expressions are the same. The following table illustrates the comparison operators that you can use to construct expressions:<\/p>\n\n\n\n
    Operator<\/th>Meaning<\/th><\/tr>
    =<\/td>Equal to<\/td><\/tr>
    <> or !=<\/td>Not equal to<\/td><\/tr>
    <<\/td>Less than<\/td><\/tr>
    ><\/td>Greater than<\/td><\/tr>
    <=<\/td>Less than or equal to<\/td><\/tr>
    >=<\/td>Greater than or equal to<\/td><\/tr><\/thead><\/table><\/figure>\n\n\n\n
    SQLite logical operators<\/h3>\n\n\n\n
    Logical operators allow you to test the truth of some expressions. A logical operator returns 1, 0, or a NULL value.<\/p>\n\n\n\n
    Notice that SQLite does not provide Boolean data type therefore 1 means TRUE, and 0 means FALSE.<\/p>\n\n\n\n
    The following table illustrates the SQLite logical operators:<\/p>\n\n\n\n
    Operator<\/th>Meaning<\/th><\/tr>
    ALL<\/td>returns 1 if all expressions are 1.<\/td><\/tr>
    AND<\/td>returns 1 if both expressions are 1, and 0 if one of the expressions is 0.<\/td><\/tr>
    ANY<\/td>returns 1 if any one of a set of comparisons is 1.<\/td><\/tr>
    BETWEEN<\/a><\/td>returns 1 if a value is within a range.<\/td><\/tr>
    EXISTS<\/a><\/td>returns 1 if a subquery contains any rows.<\/td><\/tr>
    IN<\/a><\/td>returns 1 if a value is in a list of values.<\/td><\/tr>
    LIKE<\/a><\/td>returns 1 if a value matches a pattern<\/td><\/tr>
    NOT<\/td>reverses the value of other operators such as NOT EXISTS, NOT IN, NOT BETWEEN, etc.<\/td><\/tr>
    OR<\/td>returns true if either expression is 1<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    SQLite WHERE clause examples<\/h2>\n\n\n\n
    We will use the tracks<\/code> table in the sample database<\/a> to illustrate how to use the WHERE<\/code> clause.<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    The equality operator (=<\/code>) is the most commonly used operator. For example, the following query uses the WHERE<\/code> clause the equality operator to find all the tracks in the album id 1:<\/p>\n\n\n
    SELECT<\/span>\n   name<\/span>,\n   milliseconds,\n   bytes<\/span>,\n   albumid\nFROM<\/span>\n   tracks\nWHERE<\/span>\n   albumid = 1<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"SQLite<\/figure>\n\n\n\n
    SQLite compares the values stored in the AlbumId<\/code> column with a literal value 1<\/code> to test if they are equal. Only the rows that satisfy the condition are returned.<\/p>\n\n\n\n
    When comparing two values, you need to ensure they are the same data type. You should compare numbers with numbers, string with strings, and so on.<\/p>\n\n\n\n
    In case you compare values in different data types e.g., a string with a number, SQLite has to perform implicit data type conversions, but in general, you should avoid doing this.<\/p>\n\n\n\n
    You use the logical operator to combine expressions. For example, to get tracks of the album 1 that have the length greater than 200,000 milliseconds, you use the following statement:<\/p>\n\n\n
    SELECT<\/span>\n  name<\/span>,\n  milliseconds,\n  bytes<\/span>,\n  albumid\nFROM<\/span>\n  tracks\nWHERE<\/span>\n  albumid = 1<\/span>\n  AND<\/span> milliseconds > 250000<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"SQLite<\/figure>\n\n\n\n
    The statement used two expressions albumid = 1<\/code> and milliseconds > 250000<\/code>. It uses the AND<\/code> logical operator to combine these expressions.<\/p>\n\n\n\n
    \"SQLite<\/figure>\n\n\n\n
    Using WHERE clause with LIKE operator example<\/h3>\n\n\n\n
    Sometimes, you may not remember exactly the data you want to search. In this case, you perform an inexact search using the LIKE<\/a><\/code> operator.<\/p>\n\n\n\n
    For example, to find which tracks were composed by Smith<\/code>, you use the LIKE<\/code> operator as follows:<\/p>\n\n\n
    SELECT<\/span>\n  name<\/span>,\n  albumid,\n  composer\nFROM<\/span>\n  tracks\nWHERE<\/span>\n  composer LIKE<\/span> '%Smith%'<\/span>\nORDER<\/span> BY<\/span>\n  albumid;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"SQLite<\/figure>\n\n\n\n
    You get tracks composed by R.A. Smith-Diesel, Adrian Smith, etc.<\/p>\n\n\n\n
    Using SQLite WHERE clause with the IN operator example<\/h3>\n\n\n\n
    The IN<\/a><\/code> operator allows you to check whether a value is in a list of a comma-separated list of values. For example, to find tracks that have media type id is 2 or 3, you use the IN<\/code> operator as shown in the following statement:<\/p>\n\n\n
    SELECT<\/span>\n\tname<\/span>,\n\talbumid,\n\tmediatypeid\nFROM<\/span>\n\ttracks\nWHERE<\/span>\n\tmediatypeid IN<\/span> (2<\/span>, 3<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"SQLite<\/figure>\n\n\n\n
    In this tutorial, you have learned how to use the SQLite WHERE<\/code> clause to filter rows in the final result set using comparison and logical operators.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
    Try It<\/a><\/p>\n\n\n\n
    Try It<\/a><\/p>\n\n\n\n
    Try It<\/a><\/p>\n\n\n\n
    Try It<\/a><\/p>\n\n\n\n
    Besides the SELECT<\/code> statement, you can use the WHERE<\/code> clause in the UPDATE<\/a><\/code> and DELETE<\/a><\/code> statements.<\/p>\n\n\n\n