Lecture 26 - SQL: Multi-Table Queries (JOINS)

Agenda

Today's funny (from 2015) -------->

Remember to opt in to the final showcase - you can even opt in a previous project!

MySQL Joins: learning how to handle more complex data relationships

Now back to SQL

When last we left you were discussing in pairs...

Partner 1: explain to partner 2 what SQL or MySQL is and how it relates to a full stack applications.

Partner 2: explain to partner 1 what PHP is and how it is used in a full stack application.

Together: Come up with a description of what the PDO object is and represents, why we use it, and what are some of the things we can do with it?

SQL so far

Database querying

• SELECT
• DISTINCT
• WHERE
• LIKE
• ORDER BY
• LIMIT

Database manipulation (create/insert)

• CREATE TABLE
• INSERT INTO TABLE
• UPDATE
• DELETE

Accessing SQL from PHP with the PDO object

Multi-Table Queries (JOINS)

XKCD 1810

Related tables and keys

• Primary Key: a column guaranteed to be unique for each record (e.g. Lisa Simpson's ID 888)
• Foreign Key: a column in Table A storing a primary key from table B
  • (e.g. records with grades with student_id)
• Normalizing: Splitting tables to improve structure/redundancy (linked by unique IDs)

This is covered more in depth in database courses (e.g. CSE 344 (CSE majors) and CSE 414 (non-CSE-majors)) and Info 330.

What do you think this does?

          SELECT *
          FROM students, grades
          WHERE students.id = grades.student_id;
          

SQL (example)

Demo in phpMyAdmin or our Query Tester

Visualization

You can visualize this like a venn diagram

JOIN Template

          SELECT col(s)                -- Which columns you want to display
          FROM table1, table2, ...     -- From all the tables that have your data
          WHERE table1.a = table2.b    -- A condition that combines two tables.
          AND table2.c > '42';         -- And maybe more conditions
          

SQL (template)

          SELECT students.name, grades.*
          FROM students, grades
          WHERE students.id = grades.student_id
          AND grades.grade > 'C';
          

SQL (example)

Result table

Giving Names to Tables

          SELECT students.name, grades.*
          FROM students, grades
          WHERE students.id = grades.student_id
          AND grades.grade > 'C';
          

SQL (example from previous slide)

A more compact solution (giving variable names to tables)

          SELECT s.name, g.*
          FROM students s, grades g
          WHERE s.id = g.student_id
          AND g.grade > 'C';
          

SQL (alternative example)

• You can give names to tables, like a variable in Java.
• To specify all columns in a table, write table.*

Note: An Equivalent Way to JOIN Tables

          SELECT s.name, g.*
          FROM students s, grades g
          WHERE s.id = g.student_id
          AND g.grade > 'C';
          

SQL (example from previous slide)

          SELECT s.name, g.*
          FROM students s
          JOIN grades g ON s.id = g.student_id
          WHERE g.grade > 'C';
          

SQL (alternative example, using the JOIN keyword)

The JOIN keyword is another way to join multiple tables. Some people find this more intuitive, while others find joining tables on multiple WHERE conditions more intuitive. Use whichever form you prefer most!

Note: There are other types of JOINS you may find on the recommended SQL query practice sites. You may learn these for fun if you'd like, but we don't cover it in this class (you will learn about them in other database courses). The JOIN keyword in this slide's example is also known as an "INNER JOIN"

Practice Queries

Click the text for solutions

What teachers do all of the students have? Order this by the student's name and use the WHERE

    SELECT s.name, t.name
    FROM students s, teachers t, grades g, courses c
    WHERE s.id = g.student_id
    AND g.course_id = c.id
    AND c.teacher_id = t.id
    ORDER BY s.name ASC;
                  

SQL

What teachers do all of the students have? Order this by the student's name and use the JOIN

    SELECT s.name, t.name
    FROM students s
    JOIN grades g ON s.id = g.student_id
    JOIN courses c ON g.course_id = c.id
    JOIN teachers t ON c.teacher_id = t.id
    ORDER BY s.name ASC;
                

SQL

A Suboptimal Query

Exercise: What courses have been taken by both Bart and Lisa?

          SELECT bart.course_id
          FROM grades bart, grades lisa
          WHERE lisa.course_id = bart.course_id
          AND bart.student_id = 123
          AND lisa.student_id = 888;
        

SQL

What's wrong here?

Improved Query

What courses have been taken by both Bart and Lisa?

          SELECT DISTINCT c.name
          FROM courses c, students lisa, students bart, grades g1, grades g2
          WHERE g1.course_id = c.id
          AND g1.student_id = bart.id
          AND g2.course_id = c.id
          AND g2.student_id = lisa.id
          AND bart.name = 'Bart'
          AND lisa.name = 'Lisa';
          

SQL

Why so many tables?

• These are all of the tables we will need to get the correct result. We want to treat Lisa and Bart's student and grades information distinctly, so we need to have two variables for each of these tables to join them correctly.

Designing a Query (Refer to this slide often!)

Figure out the proper SQL queries in the following way:

• Which table(s) contain the critical data? (FROM)
• Which columns to I need in the result set? (SELECT)
• How are tables connected (JOIN and/or WHERE) and values filtered (WHERE)?
• Do I need to return only DISTINCT records?
• Do I care about the order of records returned? If so, which columns do I need to sort by and in what precedence?

Example Database (also on query tester)

• imdb-small is also provided on the query tester page for testing queries with a smaller dataset

IMDB Table Relationships/ids

More information on JOINS