Joins

Remember to

• download portal_mammals.sqlite.
• connect portal_mammals.sqlite to SQLite Manager.
• display a fully joined version of the Portal data using:
  SELECT * FROM surveys JOIN species ON surveys.species_id = species.species_id JOIN plots ON surveys.plot_id = plots.plot_id;

Why use multiple tables

• It is often not efficient to include all information of interest in a single table.
• Redundant information makes it more difficult to update or revise data.
  • If something changes we want to be able to change it in one place, not hundreds of places.
• Use multiple tables
• Each table contains a single kind of information
  • surveys: information about individuals
  • species: information about species
  • plots: information about plots
• If a species name changes we only need to change it in the species table
• Connect tables using joins to describe relationships between tables (“relational” database)

Basic join

• JOIN
  • combine rows from multiple tables
  • based on condition

SELECT DISTINCT year, month, day, plot_type 
FROM surveys
JOIN species ON surveys.plot_id = plots.plot_id

• This query selects year, month, and day from surveys and plot_type from the plots table.
  • The query links the plot_id from surveys with plot_id from plots.
• ON basically works like WHERE
  • It represents a matching identifier between two tables
  • In fact, you can even use WHERE instead
  • If you don’t limit the join using ON, bad things happen, because the JOIN combines each row in surveys with every row in plots

    SELECT DISTINCT year, month, day, plot_type
    FROM surveys
    JOIN plots
  

• One way to think about this join is that it adds the information in plots to the surveys table

Do Exercise 9 - Basic Join.

• We can also use USING as short hand in cases where the column names are the same across tables.

SELECT year, month, day, genus, species
FROM surveys
JOIN species USING (species_id);

Multi-table join

• Use multiple JOINs to link multiple tables.

SELECT year, month, day, taxa plot_type
FROM surveys
JOIN species ON surveys.species_id = species.species_id
JOIN plots ON surveys.plot_id = plots.plot_id;

Multi-table join with abbreviations

• The previous SELECT statement works because each of the fields are uniquely named.
• It is safer to write a query that links fields to their table.

SELECT surveys.year, surveys.month, surveys.day, species.taxa, plots.plot_type
FROM surveys
JOIN species ON surveys.species_id = species.species_id
JOIN plots ON surveys.plot_id = plots.plot_id;

• Use abbreviations to help with readability.

SELECT sv.year, sv.month, sv.day, sp.taxa, p.plot_type
FROM surveys sv
JOIN species sp  ON sv.species_id = sp.species_id
JOIN plots p ON sv.plot_id = p.plot_id;

Do Exercise 10 - Multi-table Join.

Combining joins with WHERE, ORDER BY, and aggregation

• Joins can be combined with everything else we’ve learned about SQL

SELECT sp.genus, sp.species, COUNT(*) as number
FROM surveys sv
JOIN species sp  ON sv.species_id = sp.species_id
JOIN plots p ON sv.plot_id = p.plot_id
WHERE p.plot_type = 'Rodent Exclosure'
GROUP BY sp.genus, sp.species
HAVING number > 50
ORDER BY number;

• To build of big queries like this start small and then expand
• Test each step

Do Exercise 11 - Filtered Join.