---
title: "Basic SQL 3"
subtitle: "October 26 · Connect tables without losing the question"
author: "MaDS Databases & SQL"
format:
  revealjs:
    theme: [default, mads-sql-reveal.scss]
    slide-number: c/t
    chalkboard: true
    code-line-numbers: true
    transition: fade
    footer: "Adapted from Alex Reinhart · MADS Computing"
---

## One question will organize today

::: {.question}
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

::: {.source-note}
This course follows and adapts [Alex Reinhart's MADS Computing course](https://www.refsmmat.com/courses/msp-computing/). Today's foreign-key and join concepts follow his [SQL Basics chapter](https://www.refsmmat.com/courses/msp-computing/data-engineering/sql.html); 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`

```text
one row = one weather station
```

`observations`

```text
one row = one station on one date
```

The tables describe different entities at different grains.

## Keys create the relationship

```text
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

```sql
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

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

Then qualify columns:

```sql
s.name
o.date
o.precip
```

Aliases shorten names; they do not create new tables.

## `INNER JOIN` keeps matches

```sql
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

```sql
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

::: {.checkpoint}
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

```sql
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:

```sql
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

```sql
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

```sql
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`

```text
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

::: {.checkpoint}
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

```sql
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

```sql
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

```sql
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`

```sql
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

::: {.checkpoint}
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.
:::

```sql
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

```sql
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:

```sql
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:

```text
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

::: {.project-prompt}
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

```text
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.