Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 33 ยท Querying

Querying in SQLite

A tour of SELECT in SQLite: WHERE, ORDER BY, LIMIT, GROUP BY, case sensitivity, LIKE versus GLOB, NULL ordering and the SQLite quirks that surprise newcomers.

You already know the shape of a query from the SQL tutorial: SELECT columns FROM a table, filter with WHERE, group, sort and limit. SQLite understands that same language, so this lesson is not a first introduction. It is a quick tour of each clause with an eye on the places where SQLite behaves differently from other databases: text comparison and case, LIKE versus GLOB, where NULL sorts, loose GROUP BY, and a couple of quirks with quotes.

All examples use the bookstore sample database (authors, books, customers and orders), which is loaded fresh each time you press Run.

A query runs in this order

FROM Pick the table and join others.
WHERE Keep only the rows you want.
GROUP BY Collapse rows into groups.
ORDER, LIMIT Sort, then cut.

SELECT is written first but evaluated late.

1 / 2

SELECT, WHERE, ORDER BY and LIMIT

The four everyday clauses work as you expect. LIMIT n OFFSET m skips m rows and returns the next n. SQLite also accepts the shorthand LIMIT m, n, but with the numbers the other way around, so prefer the explicit OFFSET form for clarity.

SQL Filter, sort, limit ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first query returns Rivers of Glass (16.5) and The Salt Road (14.99). The third skips the first genre alphabetically (Children) and shows the next three: Fiction, Mystery, Poetry.

Without ORDER BY, the row order is whatever is convenient for SQLite, often by rowid. Never rely on it; add ORDER BY whenever order matters, and add a tie-breaker column when sort values can repeat.

The Nth row and the Nth highest value

LIMIT 1 OFFSET n-1 fetches the Nth row of a sorted result. Two details decide whether you get the right answer. The sort must be fully determined (add a tie-breaker such as id), and "Nth highest value" is a different question from "Nth row": with repeated prices, the third row may hold the same price as the second one.

SQL Nth row versus Nth highest value ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first query returns Islands Without Names (17.4). The next two both return Weather for Beginners (19.95), the second-highest distinct price. The last returns the book with the highest id. Remember that a large OFFSET still makes SQLite walk past all the skipped rows; for deep paging use keyset conditions such as WHERE id > last_seen_id instead.

Case sensitivity and collations

This is where SQLite most often surprises people:

  • The = operator and < compare text byte by byte, so 'a' = 'A' is false, and 'b' < 'B' is false because lowercase letters sort after uppercase ones in the default BINARY collation.
  • You can change that per comparison with COLLATE NOCASE, or permanently by declaring a column TEXT COLLATE NOCASE.
  • LIKE is case-insensitive for ASCII letters only by default. 'a' LIKE 'A' is true, but 'รฉ' LIKE 'ร‰' is false.
  • GLOB is always case-sensitive.
SQL Three kinds of text comparison

Press Run (or Ctrl + Enter) to see the result here.

You should see 0, 1, 1, 0, 0, 0. If you need real Unicode-aware case folding, SQLite does not include it in the core library: you would use an extension such as ICU, or normalize case in your application.

LIKE versus GLOB

Both match text against a pattern, but with different wildcards.

LIKE GLOB
Any string % *
One character _ ?
Character class not supported [abc], [a-c], [^0-9]
Case insensitive (ASCII) sensitive
SQL LIKE and GLOB patterns ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The first two queries return the same two titles (The Quiet Algorithm and The Salt Road). The third returns nothing because no title starts with a lowercase the. The last one finds titles starting with A, B or C.

To search for a literal % or _ in LIKE, add an escape character: title LIKE '100\%' ESCAPE '\'. Also remember that a pattern starting with % cannot use an index, so on large tables it means a full scan. For proper word search, SQLite offers full-text search.

NULL ordering and testing for NULL

In SQLite, NULL is treated as smaller than any other value. So an ascending sort puts NULL first and a descending sort puts it last. Since SQLite 3.30 you can override this with NULLS FIRST or NULLS LAST on each sort term.

SQL Where do NULLs go? ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Daniel Brook has no birth year, so he appears first in the plain ascending sort, last with NULLS LAST, and first again in the descending sort with NULLS FIRST.

To test for NULL, use IS NULL and IS NOT NULL. A comparison like born = NULL is itself NULL, never true. SQLite also lets you use IS and IS NOT between any two values as a null-safe equality: a IS b is true when both are NULL or both are equal.

Integer division and string joining

Two small details that catch people:

  • Dividing two integers gives an integer: 7 / 2 is 3. Make one side a real number to get a fraction: 7 / 2.0 is 3.5.
  • The string-concatenation operator is ||, not +. 'ab' || 'cd' is 'abcd', and if either side is NULL, the result is NULL.

GROUP BY, HAVING and the "bare column" rule

Aggregate queries work as in the SQL tutorial: GROUP BY collapses rows, HAVING filters groups, and ORDER BY can sort by aggregate results or by aliases.

SQL Groups and HAVING ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The second query shows a SQLite-specific feature. Strict databases reject it because title is neither grouped nor aggregated. SQLite allows it, and it has a useful special case: when the query has exactly one MIN() or MAX() aggregate, the bare columns come from the row that holds that minimum or maximum. So each genre is paired with its most expensive book's title. In every other situation a bare column takes its value from an arbitrary row of the group, so do not rely on it.

Quotes: single, double and the missing column

Single quotes are for string literals: 'Fiction'. Double quotes are for identifiers: "title". But SQLite has a forgiving legacy behavior: if a double-quoted name does not match any column, it is silently treated as a string literal.

SQL The double-quote surprise ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The second query does not fail; it returns the text nonexistent_col_xyz. A typo in a double-quoted column name can therefore turn a filter into a comparison with a constant string, with no error. Stick to single quotes for text, and double quotes (or square brackets) only when an identifier needs quoting. Many SQLite builds let you turn the misfeature off at compile time.

A CASE expression for labels

CASE works as in other databases and is handy for turning numbers into labels:

SQL Labels with CASE ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

For more on the clauses themselves, revisit WHERE, ORDER BY and aggregate functions in the SQL tutorial.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does 'a' = 'A' return in SQLite with default settings?

    The default BINARY collation is case-sensitive. Use COLLATE NOCASE to ignore ASCII case.
  2. 2. Which pattern matches titles starting with A, B or C?

    Character classes exist only in GLOB, and GLOB uses * as its wildcard.
  3. 3. In an ascending ORDER BY born, where do rows with a NULL born appear by default?

    SQLite treats NULL as smaller than any value, so ascending puts NULLs first. Add NULLS LAST to change it.
  4. 4. What does SELECT 7 / 2 return?

    Two integers use integer division. Write 7 / 2.0 to get 3.5.
  5. 5. A query uses WHERE "nme" = 'x' and the column is really called name. What happens?

    Unmatched double-quoted identifiers fall back to string literals, so the typo is silent. Use single quotes for text.

Summary

  • The core clauses behave as in standard SQL; always add ORDER BY when order matters.
  • = and < are case-sensitive by default; COLLATE NOCASE changes that. LIKE ignores ASCII case only; GLOB is case-sensitive and supports [...] classes.
  • NULL sorts first ascending; NULLS FIRST and NULLS LAST override it (3.30+). Use IS NULL, and IS for null-safe equality.
  • Integer division truncates; concatenate with ||.
  • Bare columns in GROUP BY are allowed, and follow the row of a single MIN()/MAX().
  • A misspelled double-quoted identifier silently becomes a string: use single quotes for text.

Next: Built-in Functions, the string, math and aggregate functions that ship with SQLite.