critwire · Learn · Learn's data

Joins f3

Prerequisites: Group and aggregate (f2)

Answer questions that need several tables at once: inner, left and anti joins, many-to-many through a junction table, and a first look at coordinates in plain SQL. Everything runs in this browser, on the tables the Schema page describes and one published file.

Already know this?

For every country in South America, show code, name, how many species have records there in the sample and how many records, countries with none included (as 0), by name. Solve it and the module is marked done.

Show answer
SELECT c.code, c.name,
       count(DISTINCT o.species_id) AS species,
       count(o.id) AS records
FROM countries AS c
LEFT JOIN occurrences AS o ON o.country_code = c.code
WHERE c.continent = 'South America'
GROUP BY c.code, c.name
ORDER BY c.name;

Concept

Normalized tables (Normalization, d2) keep each fact once, so most questions need several tables at once. A join pairs the rows of two tables wherever a condition, written after ON, is true; aliases (species AS s) keep the column names short and unambiguous. The ERD on the Schema page says which columns to join on and what to expect: the line species ||--o{ occurrences reads "one species, zero or more records", so joining a species to its records turns one row into many, and countries |o--o{ occurrences says a record has at most one country, possibly none.

Worked example

Which animals were recorded in Brazil? critwire answers it in two places. species_countries is critwire's published list, the junction between species and countries, built from every open record in GBIF after critwire's country rule:

The lab sample holds the records themselves, so it can also say how many:

The two lists differ. EXCEPT returns the rows of the first query that the second doesn't have:

Each has a single Brazilian record in the sample (the query above), far from the rest of its records. critwire's country rule leaves out a country with only one record outside the region where most of an animal's countries lie, as a likely captive animal or a mistake; the sample keeps every record. Neither list is where the animal lives: both are where someone recorded it (and the Tiger, the Chimpanzee and the African Wild Dog are on both).

Lab

  1. Which animals have no records in the sample at all? An anti join: keep the species whose left join found nothing.

    The three without a GBIF id are the ones Select and filter (f1) found: critwire hasn't settled which GBIF taxon they are, so nothing was downloaded for them. The Ili Pika has no open record anywhere. The Yangtze Giant Softshell Turtle is different: critwire lists countries for it, but its only open record comes from a dataset whose own license is CC BY-NC, and the sample keeps a record only when both the record's and its dataset's licenses are open (the lab data page). The same anti join on species_countries finds the other four, but not the turtle:

    A different kind of list shows another gap. /v1/range-listings holds what named sources list: each row is one source's claim about one animal in one country. Every mammal in critwire has rows from the Mammal Diversity Database (MDD), the American Society of Mammalogists' open list of mammal species; Licenses gives its license and where its data came from. No lab table holds this file, so the block reads it where it's published, with read_parquet('range-listings.parquet'). An animal and a country together make the key, so this anti join matches on two columns: the countries where MDD lists an animal as present, with no row in species_countries.

    On this page, native, introduced and range words describe only /v1/range-listings rows, worded as what a named source lists ("MDD lists it in Kenya as native or introduced before 1500"); records are still never range.

    These 89 rows start with the Aardvark in Burundi: MDD lists it there as native or introduced before 1500, and no open record passes critwire's country rule. The two lists answer different questions, so neither is wrong. A listing is a source's claim and a record is someone's observation; a listed country with no record shows nothing either way, since the animal may be gone or nobody may have shared a record. Likewise, no row in this file means critwire holds no open listing for that pair, not that the animal is absent.

  2. Where the condition goes. Records since 2020 in each South American country, zeros kept: the year test is part of the match, so it goes in ON.

    Move o.year >= 2020 to WHERE and the countries with no recent record vanish: their o.year is NULL, the test is unknown, and WHERE drops them. The left join has quietly become an inner join.

  3. The row multiplier, or fan-out. The Jaguar has 2,000 records and 20 countries in species_countries; joining both to the species pairs every record with every country:

  4. Light spatial in plain SQL. Every record has a longitude and latitude in degrees (WGS 84), so min and max give a species' bounding box, the smallest rectangle around its records:

    The Tasmanian Devil's box runs from New Mexico to Tasmania: a few zoo specimens in Albuquerque stretch it over 257° of longitude, the long way round through Africa and Asia (the Jaguar's northern edge is the same zoo). One outlier ruins a box, so look at where the records actually are. A rectangle as a filter (BETWEEN on both axes) finds the records in and around Tasmania:

  5. Count records in a grid of 10° boxes: floor(longitude / 10) * 10 is the west edge of a record's box, floor(latitude / 10) * 10 its south edge. floor rounds down, also below zero, so latitude -41.2 falls in the box from -50 to -40.

Result

Brazil has 12 animals in critwire's list and 14 in the sample; the two extra ones each rest on one stray record that critwire's rule leaves out. Five animals have no record in the sample, four of them for reasons range_status gives and one because of a dataset's license. A condition on the right table in WHERE turns a left join into an inner one (14 countries become 9), and joining two "many" tables at once turned 2,000 records into 40,000 rows. MDD lists 89 animal and country pairs with no open record, which settles nothing about either list. A degree box is cheap and works anywhere, but it is not an area (a 10° box near the poles is much smaller than one at the equator) and one outlier stretches it: the GIS in SQL modules (Coordinate reference systems, g1, and Spatial joins, g3) do this properly with geometries.

Exercise

Which countries in Africa have no record in the sample? Show code and name, by name. Write it with NOT EXISTS.

Show answer
SELECT c.code, c.name
FROM countries AS c
WHERE c.continent = 'Africa'
  AND NOT EXISTS (SELECT 1 FROM occurrences AS o WHERE o.country_code = c.code)
ORDER BY c.name;
About the answer

The LEFT JOIN … WHERE o.id IS NULL form passes the check too.

The NOT IN trap, in one line: whether 'BR' is in a list holding NULL can't be ruled out, so NOT IN gives unknown, while a match still makes IN true.

The other direction, across the two lists. Which countries have open records of the Tiger in species_countries but no row from any source in the listings file? Show the country's name as country, by name. Read the file with read_parquet('range-listings.parquet'); its speciesId and country are the two columns to match, in a NOT EXISTS.

Show answer
-- DuckDB only: reads the published file, not a learners' table
SELECT c.name AS country
FROM species AS s
JOIN species_countries AS sc ON sc.species_id = s.id
JOIN countries AS c ON c.code = sc.country_code
WHERE s.common_name = 'Tiger'
  AND NOT EXISTS (SELECT 1 FROM read_parquet('range-listings.parquet') AS l
                  WHERE l.speciesId = sc.species_id AND l.country = sc.country_code)
ORDER BY c.name;
About the answer

Four countries. Each is a question to ask, not a finding: a captive animal and a misplaced record look the same here, and no row means critwire holds no open listing, not that no source anywhere lists the Tiger there.

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

Schema: the browser's tables

critwire Learn schema v2, the tables in the browser

Every column, in words · The diagram alone