Basic SQL 3

October 26 · Connect tables without losing the question

MaDS Databases & SQL

One question will organize today

Which Pennsylvania stations reported precipitation on September 1—and which stations have no observation record?

The answer requires two tables and a deliberate join.

Course source and adaptation

This course follows and adapts Alex Reinhart’s MADS Computing course. Today’s foreign-key and join concepts follow his SQL Basics chapter; the weather schema and checkpoints are new course applications.

By the end of class

You should be able to:

  • identify primary and foreign keys,
  • write an INNER JOIN and a LEFT JOIN,
  • predict which rows each join preserves,
  • recognize row multiplication and silent row loss,
  • explain the grain of a joined result.

Each table has one job

stations

one row = one weather station

observations

one row = one station on one date

The tables describe different entities at different grains.

Keys create the relationship

stations.station_id       primary key
          │
          └── observations.station_id  foreign key
  • A primary key identifies one row.
  • A foreign key points to a valid row in another table.
  • One station can have many observations.

A join builds a virtual table

SELECT
  s.station_id,
  s.name,
  o.date,
  o.precip
FROM observations AS o
JOIN stations AS s
  ON o.station_id = s.station_id;

ON states how rows correspond.

Aliases keep multi-table queries readable

FROM observations AS o
JOIN stations AS s
  ON o.station_id = s.station_id

Then qualify columns:

s.name
o.date
o.precip

Aliases shorten names; they do not create new tables.

INNER JOIN keeps matches

SELECT s.name, o.date, o.precip
FROM observations AS o
INNER JOIN stations AS s
  ON o.station_id = s.station_id;

Only rows satisfying the ON condition survive.

Writing JOIN alone means INNER JOIN.

Filters apply to the joined rows

SELECT s.name, o.date, o.precip
FROM observations AS o
JOIN stations AS s
  ON o.station_id = s.station_id
WHERE s.state = 'PA'
  AND o.date = DATE '2025-09-01'
  AND o.precip IS NOT NULL;

Columns from either table can appear in WHERE.

Checkpoint 1: combine facts

Return station ID, station name, date, and precipitation for Pennsylvania observations on September 1, 2025. Exclude unknown precipitation and show the wettest first.

Use aliases s and o.

Checkpoint 1: one solution

SELECT
  s.station_id,
  s.name,
  o.date,
  o.precip
FROM observations AS o
JOIN stations AS s
  ON o.station_id = s.station_id
WHERE s.state = 'PA'
  AND o.date = DATE '2025-09-01'
  AND o.precip IS NOT NULL
ORDER BY o.precip DESC, s.name;

The result grain follows the many side

Without the date filter:

SELECT s.station_id, s.name, o.date
FROM stations AS s
JOIN observations AS o
  ON s.station_id = o.station_id;

One station appears once for every matching observation.

That is expected multiplication—not automatically an error.

Count before trusting a join

SELECT COUNT(*) FROM stations;

SELECT COUNT(*)
FROM stations AS s
JOIN observations AS o
  ON s.station_id = o.station_id;

If the row count changes, explain why.

Never assume a join preserves one row per input row.

LEFT JOIN preserves the left table

SELECT s.station_id, s.name, o.precip
FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
 AND o.date = DATE '2025-09-01'
WHERE s.state = 'PA';

Every Pennsylvania station remains, even without a match.

Unmatched right-side fields become NULL

station_id   name                 precip
-----------  -------------------  ------
USW...001    PITTSBURGH ASOS       12.4
USW...002    SOME STATION          NULL

The second row may mean no observation row—or a row with missing precipitation.

Those are different data-quality states.

Checkpoint 2: preserve stations

Return every Pennsylvania station and its precipitation on September 1, 2025, if an observation exists. Keep stations with no observation.

Then adapt the query to show only stations with no observation row on that date.

Checkpoint 2: preserve every station

SELECT s.station_id, s.name, o.precip
FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
 AND o.date = DATE '2025-09-01'
WHERE s.state = 'PA'
ORDER BY s.name;

Checkpoint 2: find no matching row

SELECT s.station_id, s.name
FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
 AND o.date = DATE '2025-09-01'
WHERE s.state = 'PA'
  AND o.station_id IS NULL
ORDER BY s.name;

Test a non-nullable right-side key to identify no match.

A right-table filter can undo a left join

FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
WHERE o.date = DATE '2025-09-01'

Unmatched rows have o.date = NULL, so WHERE removes them.

The query behaves like an inner join for that condition.

Put match rules in ON

FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
 AND o.date = DATE '2025-09-01'
WHERE s.state = 'PA'
  • ON: which right-side rows count as matches?
  • WHERE: which completed result rows should remain?

Checkpoint 3: repair silent data loss

This query claims to retain every Pennsylvania station. Repair it, then explain what must be unique for the result to contain at most one row per station.

SELECT s.station_id, s.name, o.precip
FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
WHERE s.state = 'PA'
  AND o.date = DATE '2025-09-01';

Checkpoint 3: repaired

SELECT s.station_id, s.name, o.precip
FROM stations AS s
LEFT JOIN observations AS o
  ON s.station_id = o.station_id
 AND o.date = DATE '2025-09-01'
WHERE s.state = 'PA';

Required design rule:

UNIQUE (station_id, date)

Many-to-many joins need a bridge

A storm can affect many stations. A station can be affected by many storms.

The project schema resolves this with:

storm_impacts(storm_id, station_id, date, distance_miles)

One row represents one storm–station–date relationship.

Diagnose a suspicious join

Before accepting a result, ask:

  1. What does one row represent now?
  2. Which table is preserved?
  3. Can either key repeat?
  4. Did a WHERE condition remove unmatched rows?
  5. Did the row count change as expected?

Project transfer

Join two project tables. Diagnose loss and multiplication.

Include:

  • relationship, keys, and grains,
  • pre-join and post-join counts,
  • one unmatched-row diagnostic.

Assignment 1: finish with evidence

Before submitting tomorrow:

  • run every query from a clean connection,
  • include a small result sample,
  • state the grain beside each query,
  • explain one denominator or join risk,
  • keep today’s checkpoint attempts in the file.

The pattern to keep

name each table's grain
  → identify the keys
  → choose which table must survive
  → write the match condition
  → count the result
  → inspect unmatched rows
  → explain multiplication or loss

Next: design a schema that makes correct joins easier.