Joining tables

Data lives in more than one table

Real data is split across tables: visits in one, clinics in another. To answer a question that needs both, you join them.

Two tables, one condition

A join query accesses more than one table at a time. It combines rows from one table with rows from a second table, with an expression saying which rows are to be paired. First, name both tables. Second, state the condition that pairs the rows. Third, the result has one row for each pair that matches.

• Step 1 · Name both tables · In the FROM and JOIN parts. · box
• Step 2 · State the condition · After ON: which rows pair up. · link
• Step 3 · One row per pair · Only pairs where it is true. · check

A join pairs rows that match

Here is a second made up table, clinics, with a single row: North, in the Hill District. A join pairs a row from visits with a row from clinics, wherever the condition after on is true. Visits to North match, so three rows come back. But look: there are no South visits in the result. South has no row in clinics, so the join ignores those visits.

• SELECT visits.patient,
• clinics.district
• FROM visits
• JOIN clinics
• ON visits.clinic = clinics.name;

A left outer join keeps every left row

To keep the South visits, use a left outer join. Every row of the left table, visits, appears at least once. Where there is no match in clinics, empty values are substituted for the right table's columns. The district is blank for the South rows, shown here as blank space. Six rows come back, not three.

• SELECT visits.patient,
• clinics.district
• FROM visits
• LEFT OUTER JOIN clinics
• ON visits.clinic = clinics.name;

Name the columns you want

One more habit. Select star in a join returns every column from both tables, so the clinic name appears twice. The tutorial advises listing the output columns explicitly. And in a join, qualify each column with its table name, so the query does not break if a table later gains a column with the same name.

• SELECT
• FROM visits
• JOIN clinics
• ON visits.clinic = clinics.name
• LIMIT 1;

Check yourself

Pause and think. In a left outer join, what happens to a left table row with no match? The answer: it still appears, with empty values for the right table's columns.

• It still appears, with empty values for the right table's columns
• It is dropped from the result
• It causes an error
• It is matched to the first right-table row

Keep these in mind

A join pairs rows from two tables where a condition is true. An inner join drops rows with no match. A left outer join keeps every left table row. And name your columns, qualified by table.

• A join pairs rows from two tables where a condition is true
• An inner join drops rows with no match
• A left outer join keeps every left-table row
• Name your columns, qualified by table