EasyCompilers

Vocabulary for this course

The terms the chapters assume, in plain words. You can open this from any chapter.

alias
A short second name given to a table or column in a query, so the rest of the query can refer to it briefly.
Cartesian product
Every row of one table paired with every row of another, which happens when a join has no matching rule.
column
One named field in a table, with the same kind of value in every row, such as a title or a year.
constraint
A rule a table enforces on its data, such as requiring a value or making one column unique.
distinct
A word in a query that removes repeated rows from the result, so each one appears once.
foreign key
A column that refers to the primary key of another table, so each row can be tied to one row there.
index
A stored lookup that helps the database find rows quickly without reading the whole table.
inner join
The default way of combining two tables: only pairs of rows present on both sides appear in the result.
join
A query operation that combines rows from two tables by matching values they share, usually a shared identifier.
junction table
A bridge between two tables that lets a row on either side pair with many on the other; each of its rows holds one pair.
left join
A combination that keeps every row of the first table named, filling in nulls where the second has no match.
many-to-many
A relationship where one row on each side can pair with many rows on the other, usually held in a junction table.
null
A marker for a value that is missing or unknown, which is not the same as zero or an empty string.
outer join
A join that keeps rows from one or both tables even when no row on the other side matches.
primary key
The column or columns that together identify each row uniquely, and which can never be missing.
query
A request written in SQL that asks the database for an answer, usually a table of rows.
row
One record in a table, with one value for each column that table defines.
schema
The stored plan of a database: its tables, the columns in each, and the rules those columns must follow.
table
A named set of rows that all share the same columns, such as one holding every book in a library.

SQL from the ground up

Patterns, ranges and missing values

LIKE for a pattern rather than a word, IN and BETWEEN for the two shapes a condition often takes, and the right way to ask about a value that is not there.

Part 4 of 12

Beginner15 min readBack to Courses

LIKE matches a shape

= wants the whole value. LIKE wants a pattern: % stands for any run of characters, _ for exactly one. title LIKE 'The%' finds every title that starts with those three letters, and LIKE '%Darkness%' finds the word anywhere in the title.

Unlike =, LIKE compares letters case-insensitively for plain ASCII, so a search typed in lower case still finds a capitalised title. The two wildcards are the entire vocabulary, and they are enough for most searching.

IN and BETWEEN

A list of alternatives reads badly with OR: author = 'A' OR author = 'B' OR author = 'C'. IN says it once — WHERE author IN ('A', 'B', 'C') — and any value in the list passes.

BETWEEN describes a range and is inclusive at both ends: WHERE year BETWEEN 1960 AND 1980 keeps 1960 and 1980 themselves, and everything between. It works on dates too, as long as they are stored in a sortable text form like ISO dates.

The value that is not there

A column with no value stores NULL, and NULL is not a value — it is the absence of one. That is why WHERE series = NULL never matches anything: the comparison is unknown, not true. The question is asked with IS NULL and IS NOT NULL.

This is the one place SQL is strict about itself. Every other comparison either matches or it does not; a comparison with NULL is a third answer, and the row is kept only when the answer is a definite yes.

How a pattern is matched

LIKE compares the whole value with the pattern, not a part of it. The pattern is built from literal characters, % for any run of characters including none, and _ for exactly one character. 'Dune Messiah' matches 'Dune%', but '%Dune' would ask for a value that ends with those four letters.

The comparison is case-insensitive for ASCII letters, which is convenient for search and surprising when case matters. SQLite's GLOB is the case-sensitive cousin, and LIKE ... ESCAPE lets a backslash make a literal % or _ instead of a wildcard.

A pattern that begins with % cannot use an index, because the database cannot know where the match starts. A prefix pattern can, which is why LIKE 'Dune%' is cheaper than LIKE '%Dune%' on a large table.

Missing values spread through a condition

NOT IN looks like the safe way to say "none of these", but it has a trap. If the list contains NULL, every comparison against it is unknown, and the test can never be true — the query returns no rows, not the rows you excluded. The positive form is subtler: year IN (1965, NULL) can still match 1965, because one true comparison is enough.

NOT EXISTS does not have that problem, because it asks whether a row exists rather than comparing values. When a subquery might produce NULL, that is the form to reach for.

The same three-valued logic runs through AND and OR: true or unknown is true, false and unknown is false, and everything else stays unknown. A WHERE keeps only the rows whose answer is a definite yes.

Ranges of text, not just numbers

BETWEEN is not only for numbers. On text it means alphabetical betweenness, which is useful for codes and names and dangerous for dates stored in the wrong shape: BETWEEN '01/01/2020' AND '31/12/2020' compares the first characters, so it will happily include a date from 2021. ISO dates avoid the trap because their text order is their time order.

The other edge is the end of a range that holds times. BETWEEN '2020-01-01' AND '2020-12-31' leaves out anything recorded later on the 31st, because that timestamp is greater than the bare date. The safe shape is >= '2020-01-01' AND < '2021-01-01', which includes the whole final day without naming its last second.

A pattern, a list, a range, an absence

SELECT title FROM books WHERE title LIKE 'The%';
SELECT title FROM books WHERE author IN ('Ursula K. Le Guin', 'Octavia E. Butler');
SELECT title, year FROM books WHERE year BETWEEN 1960 AND 1980;
SELECT title FROM books WHERE series IS NULL;

Three mistakes this chapter invites: writing LIKE 'Dune' with no wildcard, which is just a case-insensitive equals; putting a NULL in a NOT IN list, which makes the whole condition return nothing; and comparing dates stored as DD/MM/YYYY text, where alphabetical order is not chronological order.

Try these next

  • Find every title whose second letter is u, using _ for the first character.
  • Search for a title that contains a literal percent sign, using LIKE ... ESCAPE.
  • Write the same exclusion with NOT IN and with NOT EXISTS, then add a NULL to the list and compare.

Search and absence

Write two queries: one for every book whose title starts with the word The, and one for every book with no series set.

Live Preview

Codes, one character at a time

Run the three shelf queries. Then change the first pattern to `QA-1%` and explain why a second row appears.

Live Preview

Put the wildcards where the uncertainty is. A leading % cannot use an index, so a search that starts with a known prefix — LIKE 'Dune%' — is both more precise and faster on a large table.

Check what you learned

One question at a time. A wrong answer explains itself — take another go.

Part 1 of 5

What does LIKE 'Dark%' match?