Documentation Source Text

}

HEADING 1 {WHERE clause analysis} where_clause

PARAGRAPH {
  ^The WHERE clause on a query is broken up into "terms" where each term
  is separated from the others by an AND operator.
  ^If the WHERE clause is composed of constraints separate by the OR
  operator then the entire clause is considered to be a single "term"
  to which the <a href="#or_opt">OR-clause optimization</a> is applied.
}
PARAGRAPH {
  All terms of the WHERE clause are analyzed to see if they can be
  satisfied using indices.
  Terms that cannot be satisfied through the use of indices become
  tests that are evaluated against each row of the relevant input
  tables.  No tests are done for terms that are completely satisfied by
  indices.  Sometimes
  one or more terms will provide hints to indices but still must be
  evaluated against each row of the input tables.
}

PARAGRAPH {
  The analysis of a term might cause new "virtual" terms to
  be added to the WHERE clause.  Virtual terms can be used with
  indices to restrict a search.  But virtual terms never generate code
  that is tested against input rows.
}

PARAGRAPH {
  To be usable by an index a term must be of one of the following
  forms:
}
SYNTAX {
  /column/ = /expression/
  /column/ > /expression/
  /column/ >= /expression/
  /column/ < /expression/
................................................................................
  /expression/ >= /column/
  /expression/ < /column/
  /expression/ <= /column/
  /column/ IN (/expression-list/)
  /column/ IN (/subquery/)
  /column/ IS NULL
}
PARAGRAPH {
  ^(If an index is created using a statement like this:
}
CODE {
  CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
}
PARAGRAPH {
  Then the index might be used if the initial columns of the index
................................................................................
}
SYNTAX {
  rowid IN (SELECT rowid FROM /table/ WHERE /expr1/
            UNION SELECT rowid FROM /table/ WHERE /expr2/
            UNION SELECT rowid FROM /table/ WHERE /expr3/)
}
PARAGRAPH {
  ^The rewritten expression above is conceptual; WHERE clauses containing
  OR are not really rewritten this way.
  ^The actual implemention of the OR clause uses a mechanism that is
  more efficient than subqueries and which works even 
  even for tables where the "rowid" column name has been 
  overloaded for other uses and no longer refers to the real rowid.
  ^But the essence of the implementation is captured by the statement
  above:  Separate indices are used to find candidate result rows
  from each OR clause term and the final result is the union of
  those rows.
}
PARAGRAPH {
  Note that in most cases, SQLite will only use a single index for each
  table in the FROM clause of a query.  The second OR-clause optimization
................................................................................
      of an indexed column with [affinity | TEXT affinity].</li>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>^The build-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must use the default BINARY
      collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must use the default BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must use the
      built-in NOCASE collating sequence.</li>
  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
}
PARAGRAPH {)^
  ^(But if the case_sensitive_like pragma is enabled as follows:
}
CODE {
  PRAGMA case_sensitive_like=ON;
}
PARAGRAPH {)^
  Then the LIKE operator pays attention to case and the example above would
  evaluate to false.  ^Note that case insensitivity only applies to
  latin1 characters - basically the upper and lower case letters of English
  in the lower 127 byte codes of ASCII.  ^International character sets
  are case sensitive in SQLite unless a application-defined
  [collating sequence] and [like | like() SQL function] are provided that
  take non-ASCII characters into account.
  ^But if an application-defined collating sequence and/or like() SQL
  function are provided, the LIKE optimization described here will never
................................................................................
  The LIKE operator is case insensitive by default because this is what
  the SQL standard requires.  You can change the default behavior at
  compile time by using the [SQLITE_CASE_SENSITIVE_LIKE] command-line option
  to the compiler.
}
PARAGRAPH {
  ^(The LIKE optimization might occur if the column named on the left of the
  operator uses the BINARY collating sequence (which is the default) and
  case_sensitive_like is turned on.  Or the optimization might occur if
  the column uses the built-in NOCASE collating sequence and the 
  case_sensitive_like mode is off.  These are the only two combinations
  under which LIKE operators will be optimized.)^  ^If the column on the
  right-hand side of the LIKE operator uses any collating sequence other
  than the built-in BINARY and NOCASE collating sequences, then no optimizations
  will ever be attempted on the LIKE operator.
}
PARAGRAPH {
  ^The GLOB operator is always case sensitive.  ^The column on the left side
  of the GLOB operator must always use the built-in BINARY collating sequence
  or no attempt will be made to optimize that operator with indices.
}
PARAGRAPH {

}

HEADING 1 {WHERE clause analysis} where_clause

PARAGRAPH {
  ^The WHERE clause on a query is broken up into "terms" where each term
  is separated from the others by an AND operator.
  If the WHERE clause is composed of constraints separate by the OR
  operator then the entire clause is considered to be a single "term"
  to which the <a href="#or_opt">OR-clause optimization</a> is applied.
}
PARAGRAPH {
  ^All terms of the WHERE clause are analyzed to see if they can be
  satisfied using indices.
  ^Terms that cannot be satisfied through the use of indices become
  tests that are evaluated against each row of the relevant input
  tables.  ^No tests are done for terms that are completely satisfied by
  indices.  ^Sometimes
  one or more terms will provide hints to indices but still must be
  evaluated against each row of the input tables.
}

PARAGRAPH {
  ^The analysis of a term might cause new "virtual" terms to
  be added to the WHERE clause.  ^Virtual terms can be used with
  indices to restrict a search.  ^But virtual terms never generate code
  that is tested against input rows.
}

PARAGRAPH {
  ^(To be usable by an index a term must be of one of the following
  forms:
}
SYNTAX {
  /column/ = /expression/
  /column/ > /expression/
  /column/ >= /expression/
  /column/ < /expression/
................................................................................
  /expression/ >= /column/
  /expression/ < /column/
  /expression/ <= /column/
  /column/ IN (/expression-list/)
  /column/ IN (/subquery/)
  /column/ IS NULL
}
PARAGRAPH {)^
  ^(If an index is created using a statement like this:
}
CODE {
  CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
}
PARAGRAPH {
  Then the index might be used if the initial columns of the index
................................................................................
}
SYNTAX {
  rowid IN (SELECT rowid FROM /table/ WHERE /expr1/
            UNION SELECT rowid FROM /table/ WHERE /expr2/
            UNION SELECT rowid FROM /table/ WHERE /expr3/)
}
PARAGRAPH {
  The rewritten expression above is conceptual; WHERE clauses containing
  OR are not really rewritten this way.
  The actual implemention of the OR clause uses a mechanism that is
  more efficient than subqueries and which works even 
  for tables where the "rowid" column name has been 
  overloaded for other uses and no longer refers to the real rowid.
  But the essence of the implementation is captured by the statement
  above:  Separate indices are used to find candidate result rows
  from each OR clause term and the final result is the union of
  those rows.
}
PARAGRAPH {
  Note that in most cases, SQLite will only use a single index for each
  table in the FROM clause of a query.  The second OR-clause optimization
................................................................................
      of an indexed column with [affinity | TEXT affinity].</li>
  <li>^The right-hand side of the LIKE or GLOB must be either a string literal
      or a [parameter] bound to a string literal
      that does not begin with a wildcard character.</li>
  <li>^The ESCAPE clause cannot appear on the LIKE operator.</li>
  <li>^The build-in functions used to implement LIKE and GLOB must not
      have been overloaded using the sqlite3_create_function() API.</li>
  <li>^For the GLOB operator, the column must be indexed using the 
      built-in BINARY collating sequence.</li>
  <li>^For the LIKE operator, if [case_sensitive_like] mode is enabled then
      the column must indexed using BINARY collating sequence, or if
      [case_sensitive_like] mode is disabled then the column must indexed
      using built-in NOCASE collating sequence.</li>
  </ol>
}
PARAGRAPH {
  The LIKE operator has two modes that can be set by a
  [case_sensitive_like | pragma].  ^The
  default mode is for LIKE comparisons to be insensitive to differences
  of case for latin1 characters.  ^(Thus, by default, the following
................................................................................
}
PARAGRAPH {)^
  ^(But if the case_sensitive_like pragma is enabled as follows:
}
CODE {
  PRAGMA case_sensitive_like=ON;
}
PARAGRAPH {
  Then the LIKE operator pays attention to case and the example above would
  evaluate to false.)^  ^Note that case insensitivity only applies to
  latin1 characters - basically the upper and lower case letters of English
  in the lower 127 byte codes of ASCII.  ^International character sets
  are case sensitive in SQLite unless a application-defined
  [collating sequence] and [like | like() SQL function] are provided that
  take non-ASCII characters into account.
  ^But if an application-defined collating sequence and/or like() SQL
  function are provided, the LIKE optimization described here will never
................................................................................
  The LIKE operator is case insensitive by default because this is what
  the SQL standard requires.  You can change the default behavior at
  compile time by using the [SQLITE_CASE_SENSITIVE_LIKE] command-line option
  to the compiler.
}
PARAGRAPH {
  ^(The LIKE optimization might occur if the column named on the left of the
  operator is indexed using the built-in BINARY collating sequence and
  case_sensitive_like is turned on.  Or the optimization might occur if
  the column is indexed using the built-in NOCASE collating sequence and the 
  case_sensitive_like mode is off.  These are the only two combinations
  under which LIKE operators will be optimized.)^



}
PARAGRAPH {
  ^The GLOB operator is always case sensitive.  ^The column on the left side
  of the GLOB operator must always use the built-in BINARY collating sequence
  or no attempt will be made to optimize that operator with indices.
}
PARAGRAPH {