Select and filter f1

• Browser
• 1 hour

Read a table: pick its rows and columns, sort them, take a page of them, and handle missing values. Everything runs in this browser, on the tables the Schema page describes.

Already know this?

Show the id, common_name and photo_license of every animal whose photo isn't under a Creative Commons license (the license doesn't start with CC), animals with no photo included, by common name. Solve it and the module is marked done.

SELECT id, common_name, photo_license
FROM species
WHERE photo_license NOT LIKE 'CC%' OR photo_license IS NULL
ORDER BY common_name;

Concept

A query reads tables and returns a new table; it never changes the data. Its clauses run in a fixed order, whatever order you write them in: FROM picks the table, WHERE keeps the rows whose condition is true, SELECT picks and names the columns, ORDER BY sorts, and LIMIT keeps the first rows. A table has no order of its own, so a result without ORDER BY can come back in any order, and "the first five" means nothing without it. Sort on something unique (add id last), so that ties always fall the same way.

NULL is a missing value: not zero, not empty text, but "unknown". Any comparison with an unknown value is itself unknown, neither true nor false, and WHERE keeps only the rows where the condition is true. So = NULL never matches, and NOT LIKE 'CC%' silently drops the rows whose license is NULL, because "is an unknown license not CC?" is unknown too. Test for a missing value with IS NULL or IS NOT NULL; coalesce(x, 'default') replaces one.

Worked example

Which animals have no photo? photo_url is NULL where critwire has none:

The same question asked with = finds nothing, whatever the data:

Why: each comparison below involves an unknown, so its answer is unknown (shown as NULL), except IS NULL, which asks exactly that question, and coalesce, which picks the first known value.

Lab

Which mammals does critwire list, by common name?

1. species has no class column; the view species_taxonomy has one row per species with its class, order, family and genus. Look at its first rows:

   Query f1-taxonomy SELECT * FROM species_taxonomy ORDER BY species_id LIMIT 5;

   RunStopReset databaseSchema

2. Keep the species whose id is among the mammals' ids. IN (…) is true when a value is in a list, and the list can be another query's result. Joins (f3) are the usual way to do this.

   Query f1-mammals -- show: cards SELECT id, common_name, scientific_name FROM species WHERE id IN (SELECT species_id FROM species_taxonomy WHERE class = 'Mammalia') ORDER BY common_name, id;

   RunStopReset databaseSchema

3. A page shows a few rows at a time: LIMIT takes a page and OFFSET skips the pages before it. The second page of five:

   Query f1-page -- show: cards SELECT id, common_name, scientific_name FROM species WHERE id IN (SELECT species_id FROM species_taxonomy WHERE class = 'Mammalia') ORDER BY common_name, id LIMIT 5 OFFSET 5;

   RunStopReset databaseSchema

Result

Two animals have no photo, and = NULL counts zero of them. The mammals come back A to Z; the second page is rows 6 to 10 of that same order, which is only stable because the sort ends on a unique column. In "Already know this?", dropping OR photo_license IS NULL loses exactly the two animals with no photo: a mistake that looks like a correct result.

Exercise

critwire links each animal to other catalogues through outside ids (gbif_id, ncbi_id, inaturalist_id, wikidata_id). Which animals are missing at least one of them? Show id, common_name and the four ids, by id.

SELECT id, common_name, gbif_id, ncbi_id, inaturalist_id, wikidata_id
FROM species
WHERE gbif_id IS NULL OR ncbi_id IS NULL OR inaturalist_id IS NULL OR wikidata_id IS NULL
ORDER BY id;

Check

Your rows must equal the answer's, column by column, in any row order. "Already know this?" is checked the same way. Check runs the answer on the tables as loaded, so if you changed them in the playground, Reset database first.

Dialects

• PostgreSQL: as here. Ascending sorts put NULL last (NULLS FIRST changes that); LIKE is case-sensitive and ILIKE isn't; LIMIT … OFFSET …, or the standard OFFSET … FETCH FIRST n ROWS ONLY.
• DuckDB (this page's engine): as PostgreSQL for everything in this module: NULL sorts last, LIKE and ILIKE, LIMIT and OFFSET. It adds conveniences such as SELECT * EXCLUDE (photo_url).
• SQL Server: SELECT TOP (5) …, or ORDER BY … OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; NULL sorts first in ascending order and there is no NULLS LAST; LIKE follows the column's collation, usually case-insensitive; = NULL never matches, as here, under the default ANSI_NULLS ON.

Schema: the browser's tables

Every column, in words · The diagram alone

Close

Mark done