Choosing columns, filtering rows

Name the columns

SELECT takes a list, not just one column: SELECT title, year FROM books; comes back with those two, in that order, however the table happens to be laid out. Naming what you need keeps the answer readable and keeps the query from breaking when the table grows a column.

A column can be renamed on the way out with AS: SELECT title AS book, year AS published FROM books;. The name is only for the result — nothing in the table changes.

WHERE keeps the rows that match

The WHERE clause runs a test on every row and keeps the ones that pass. The tests are the comparisons you would expect: =, <> for not-equal, <, >, <=, >=. Text goes in single quotes; numbers stand bare.

Conditions combine with AND, OR and NOT. They are not equal partners — AND binds tighter than OR — so when the intent is "either of these, and also that", write the parentheses: WHERE (year < 1950 OR year > 2000) AND author = 'Ursula K. Le Guin'.

The test is applied to one row at a time and knows nothing about the other rows. That is why WHERE cannot ask about a total or a count: those exist only after the rows have been folded together, which is what HAVING is for later in the course.

The order the database thinks in

SQL is written SELECT ... FROM ... WHERE ..., but it is evaluated the other way round: the database first finds the table, then tests each row with WHERE, and only then builds the columns the SELECT names. That is why a WHERE clause cannot normally use an alias defined in the SELECT list — the alias does not exist yet at that point.

It also explains why filtering is the cheap place to be precise. A row rejected by WHERE never reaches the sorting, grouping or output stages, so a good filter does less work than a good SELECT list.

Comparing values of different types

SQLite is relaxed about types: a column declared INTEGER can hold text, and year = '1965' may match because the comparison converts one side. That convenience hides two problems. The query may succeed here and fail on a stricter database, and a conversion on the column side — WHERE CAST(year AS TEXT) = '1965' — throws away any index built on year, because the database has to compute the expression for every row before it can compare anything.

Compare a column with a value of its own type, and let the literal do the converting rather than the column. When the stored data really is mixed, typeof(year) will show you which rows are the odd ones.

A filter is only as good as its shape

An index is a sorted structure keyed on a column, and a condition can use it only when the column appears bare on one side of the comparison. WHERE year > 1960 can seek straight to the right place. WHERE year + 10 > 1970 cannot, because the database would have to compute a new value for every row first.

The same is true of WHERE upper(author) = 'DUNE', of a pattern that begins with %, and of an OR that names two different columns. None of these is wrong; they are simply full scans. On a hundred rows that is invisible, and on a million it is the difference between a page that loads and one that does not.

One filter, two conditions

SELECT title, year
FROM books
WHERE year > 1960
  AND year < 1980;

SELECT title, author
FROM books
WHERE author = 'Octavia E. Butler'
   OR author = 'Frank Herbert';

Patterns, lists and case

LIKE matches a pattern: % stands for any run of characters and _ for exactly one. title LIKE 'The %' matches every title that begins with "The ". IN is a set of alternatives, and BETWEEN is an inclusive range — both are shorthand for a chain of OR/comparisons.

CASE turns a condition into a value, so it can sit in a SELECT list or an ORDER BY. It is an expression, not a control-flow statement: every branch has to answer a value of the same kind.

Try these next

• Find every title that contains the word "the" anywhere, using LIKE '%the%'.
• Use IN to match three authors and compare it with the same query written with OR.
• Add a CASE column that labels books before 1970 as "classic" and the rest as "modern".