Exercise 6: Join Many Tables

Hopefully you're getting your head around selecting data out of tables. Always remember this: SQL ONLY KNOWS TABLES. SQL LOVES TABLES. SQL ONLY RETURNS TABLES. TABLES. TABLES. TABLES. TABLES! I repeat this in this rather crazy manner so that you will start to realize that what you know in programming isn't going to help. In programming, you deal in graphs and in SQL you deal in tables. They're related concepts, but the mental model is different.

Here's an example of where it becomes different. Imagine you want to know what pets Zed owns. You need to write a SELECT that looks in person and then "somehow" finds Zed's pets. To do that you have to query the person_pet table to get the id columns you need. To do that you need to either join the three tables (person, person_pet, pet) together with equality expressions, or you have to do a second select to return the correct pet.id numbers.

Here's how I'd do it with a join:

/* normal join with equality */
SELECT pet.id, pet.name, pet.age, pet.dead
    FROM pet, person_pet, person
    WHERE
    pet.id = person_pet.pet_id AND
    person_pet.person_id = person.id AND
    person.first_name = 'Zed';

/* using a sub-select */
SELECT pet.id, pet.name, pet.age, pet.dead
    FROM pet
    WHERE pet.id IN
    (
        SELECT pet_id FROM person_pet, person 
        WHERE person_pet.person_id = person.id
        AND person.first_name = 'Zed'
    );

Now this looks like a lot, but I'll break it down so you can see it's simply crafting a new table based on data in the three tables and the WHERE clause:

• ex6.sql:1: A simple comment telling you what this query does.

• ex6.sql:2: I only want some columns from pet so I specify them in the select. In the last exercise you used '*' to say "every column" but that's going to be a bad idea here. Instead, you want to be explicit and say what column from each table you want, and you do that by using table.column as in pet.name.

• ex6.sql:3: To connect pet to person I need to go through the person_pet relation table. In SQL that means I need to list all three tables after the FROM.

• ex6.sql:4: Start the WHERE clause.

• ex6.sql:5: First I connect pet to person_pet by the related id columns pet.id and person_pet.id.

• ex6.sql:6: AND I need to connect person to person_pet in the same way. Now the database can search for only the rows where the id columns all match up, and those are the ones that are connected.

• ex6.sql:7: First_name test for my first name.AND I finally ask for only the pets that I own by adding a person.first_name test for my first name.

In the first SQL SELECT I'm joining the three tables by setting different id columns equal. This links them together so that the rows "line up" and are connected. The second SELECT then uses a sub-select to do the same thing:

• ex6.sql:9: A comment again telling you what's going on.

• ex6.sql:10: The exact same start to the SELECT as on line 2.

• ex6.sql:11: However, we only want data from the pet table in the "master select" because we'll be using the IN keyword to start a sub-select to get the pet.id values we need.

• ex6.sql:12: The WHERE clause then says pet.id IN which tells SQLite3 that we are going to take the pet.id values we need from another SQL query.

• ex6.sql:13: I then start this sub-select for the IN with a parenthesis.

• ex6.sql:14: I now need to only get the right pet_id values from person and person_pet using a simpler join. Just like with any SELECT I list out the columns I want and what tables they are FROM.

• ex6.sql:15: I need a where clause that sets the equality needed, but I only need to worry about person.id being matched up with person_pet.person_id.

• ex6.sql:16: AND finally my name "Zed" to get just the animals I own.

• ex6.sql:17: Then we close off this sub-select with ) and end the whole SQL statement with ;.

What You Should See

I rebuild the database again using all the .sql files I've made so far and then run the queries two ways:

$ rm ex6.db
$ sqlite3 ex6.db < code/ex2.sql
$ sqlite3 ex6.db < code/ex3.sql
$ sqlite3 ex6.db < code/ex4.sql
$ sqlite3 ex6.db < code/ex6.sql
0|Fluffy|1000|0
1|Gigantor|1|1
0|Fluffy|1000|0
1|Gigantor|1|1
$ sqlite3 -column -header ex6.db < code/ex6.sql 
id          name        age         dead      
----------  ----------  ----------  ----------
0           Fluffy      1000        0         
1           Gigantor    1           1         
id          name        age         dead      
----------  ----------  ----------  ----------
0           Fluffy      1000        0         
1           Gigantor    1           1

If you don't get exactly the same data, then do a SELECT on the person_pet table and make sure it's right. You might have inserted too many values into it.

Study Drills

• This may be a mind blowing weird way to look at data if you already know a language like Python or Ruby. Take the time to model the same relationships using classes and objects then map it to this setup.

• Do a query that finds your pets you've added thus far.

• Change the queries to use your person.id instead of the person.name like I've been doing.

Portability Notes

There are actually other ways to get these kinds of queries to work called "joins". I'm avoiding those concepts for now because they are insanely confusing. Just stick to this way of joining tables for now and ignore people who try to tell that this is somehow slower or "low class".