Sample Video Frame

Created by Zed A. Shaw Updated 2024-10-08 04:45:56

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:

View Source file ex6.sql Only

/* 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:

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:

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:

View Source file ex6.sh-session Only

$ 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

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".

Previous Lesson Next Lesson

Register for Learn SQL the Hard Way

Register today for the course and get the all currently available videos and lessons, plus all future modules for no extra charge.