Simplified
Simplified

L4.1Which table's id do you mean?

YOU

Which table's id do you mean?

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
table.column
names a column by the table it's in
attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fayusers.id
postsidcaption79Gym day80New desk81Beach daycommentsidtext601Nice desk602So cool!
attemptsiduser_idlesson_id111212321413541622741attempts.lesson_idattempts.user_idattempts.idusersidname1Ada2Ben3Cy4Dee5Eli6Fayusers.nameusers.id

Databases · Lesson 4.1

Reading Across Tables

Which table's id do you mean?

Simplified
0:00 / 1:49

So every row of attempts now holds a user ID: the id from the learner's row in users.

Ada's id is 1, so her three answers are three rows with user ID 1.

And her name is stored just once, in her row in users.

Now say Simplified's team wants its quiz analytics page to list every answer with the learner's name.

attempts holds Ada's 1, but her name, Ada, is in users.

So one query has to read columns from both tables.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay

But both tables have a column called id.

In attempts, id is each answer's own number, from 1 to 7.

In users, id is each learner's number, from 1 to 6.

So in attempts, id 2 is one of Ada's answers, but in users, id 2 is Ben.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay

So when a query reads both tables, how does it say which id it means?

It writes the table's name, then a dot, then the column's name.

table.column names a column by the table it's in.

So users.id is the id column of users.

And attempts.id is the id column of attempts.

Every other column gets its table's name the same way, like attempts.user ID and users.name.

attemptsiduser_idlesson_id111212321413541622741attempts.lesson_idattempts.user_idattempts.idusersidname1Ada2Ben3Cy4Dee5Eli6Fayusers.nameusers.id

So how might Instagram tell a post's id from a comment's?

Say Instagram keeps posts in one table and comments in another, and both have an id.

postsidcaption79Gym day80New desk81Beach dayposts.idcommentsidtext601Nice desk602So cool!comments.id

Your friend's post captioned Beach day might have id 81, in posts.

And your comment on it, "So cool!", might have id 602, in comments.

So posts.id is 81, and comments.id is 602.

table.column says which table each column comes from, even when two tables share a name.

Example: Which table's id do you mean?

Simplified's attempts table has the columns id, user_id and lesson_id, and its users table has id, email and name. One row of attempts has id 5, user_id 4 and lesson_id 1.

What value is in users.id for the learner who gave that answer?

Explanation

First, attempts.user_id holds the id from the learner's row in users, and this row's user_id is 4.

Then, users.id is the id column of users, so that learner's row has 4 in it, not the attempt's own 5.

Therefore, users.id is 4.

Question 1

Simplified's users table has the columns id, email, name, xp and created_at. Its attempts table has the columns id, user_id and lesson_id.

How many column names appear in both tables?

column names

Reading two tables side by side

attemptsiduser_idlesson_id111212321413541622741attempts.user_idusersidname1Ada2Ben3Cy4Dee5Eli6Fay
FROM attemptsattemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay
SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.id;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fayresultnamelesson_idAda1Ada2Ben1Ada3Dee1Ben2Dee1
SELECT accounts.username, comments.textFROM commentsJOIN accounts ON comments.account_id = accounts.id;commentsidaccount_idtext602205So cool!603412Love itaccountsidusername205sam.codes377maya.dev412jo.writesresultusernametextsam.codesSo cool!
SELECT users.name, attempts.lesson_idFROM attemptsJOIN usersON attempts.user_id = users.id;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fayresultnamelesson_idAda1Ada2Ben1Ada3Dee1Ben2Dee1

Databases · Lesson 4.2

Reading Across Tables

Reading two tables side by side

Simplified
0:00 / 2:43

So attempts.user ID names the user ID column of attempts, and users.id the id column of users.

Attempt 1 holds user ID 1, because Ada gave that answer, and her row's id is 1.

So the 1 in attempts.user ID and the 1 in users.id point at the same learner.

So how does one query put each attempt beside its learner's name, for the quiz analytics page?

It reads from attempts, then writes JOIN and the second table: FROM attempts JOIN users.

JOIN pairs each row of one table with each row of the other.

attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay6 pairs

Take attempt 1, with user ID 1.

JOIN pairs it with Ada's row, then Ben's, then Cy's, all the way to Fay's.

That's six pairs, one for each user.

But attempt 1 is Ada's answer, so only one of those pairs is right.

So how does Postgres know which pair to keep?

After the JOIN comes ON, with two columns: attempts.user ID equals users.id.

ON keeps only the pairs where the columns match.

ON attempts.user_id = users.idattemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fay1 = 1 ✓1 ≠ 21 ≠ 31 ≠ 41 ≠ 51 ≠ 6

Attempt 1's user ID is 1, and Ada's id is 1: they match, so that pair stays.

Ben's id is 2, and 1 isn't 2, so that pair goes.

Cy's 3, Dee's 4, Eli's 5 and Fay's 6 don't match either.

So attempt 1 keeps exactly one pair: attempt 1 and Ada.

So what does the whole query send back?

Its SELECT names users.name and attempts.lesson ID, so each row holds a name and a lesson.

SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.id;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fayresultnamelesson_idAda1Ada2Ben1Ada3Dee1Ben2Dee1

JOIN pairs every attempt with all six users: 7 times 6 is 42 pairs.

Each attempt holds one user ID, so ON keeps one pair for each.

So the result has one row per matching pair: 7 rows.

Ada is in it three times, because three attempts hold her id. Ben and Dee are in it twice.

But Cy, Eli and Fay aren't in it: no attempt holds their ids, 3, 5 or 6.

So how might Instagram show who wrote each comment under a post?

Say each row of Instagram's comments table holds the account ID of the account that wrote it.

SELECT accounts.username, comments.textFROM commentsJOIN accounts ON comments.account_id = accounts.id;commentsidaccount_idtext602205So cool!603412Love itaccountsidusername205sam.codes377maya.dev412jo.writesresultusernametextsam.codesSo cool!jo.writesLove it

So Instagram might JOIN comments to accounts, ON comments.account ID equals accounts.id.

Your comment 602, "So cool!", holds account ID 205.

It pairs with every account, but only yours, sam.codes, has id 205.

So "So cool!" shows up under your username, sam.codes.

A join sends back one row for every pair that passes ON, across both whole tables.

Example: Reading two tables side by side

Simplified's attempts table has 9 rows, one for each answer a learner gave, and their user_id values are 1, 1, 2, 1, 4, 2, 4, 3 and 3. Its users table has six rows, with ids 1 to 6: Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id;.

How many rows come back in the result?

Explanation

First, JOIN pairs each attempt with all six users, and ON keeps the pair whose users.id matches its user_id.

Then, every attempt's user_id matches exactly one user, so each of the 9 attempts keeps one pair.

Therefore, 9 rows come back, one per matching pair.

Question 1

Say Instagram's comments table has 5 rows, and its users table has 4 rows. Its database gets SELECT users.username, comments.text FROM comments JOIN users ON comments.user_id = users.id;.

Before ON keeps the matches, how many pairs does JOIN make?

pairs

Question 2

Simplified's attempts table has 6 rows, and their user_id values are 1, 3, 3, 2, 3 and 5. Its users table has six rows: Ada (id 1), Ben (2), Cy (3), Dee (4), Eli (5) and Fay (6). Its server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id;.

How many rows of the result hold the name Cy?

rows

Question 3

Simplified's users table has five rows: Ada (id 1), Ben (2), Cy (3), Dee (4) and Eli (5). Its attempts table has three rows, with user_id 2, 5 and 2. Its server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id;.

Which learners are not in the result at all?

Question 4

Say Spotify's songs table has the columns id, title and album_id, and its albums table has id and name. Its database gets SELECT songs.title, albums.name FROM songs JOIN albums ON songs.album_id = albums.id;. The song Lunch holds album_id 30.

Of Lunch's pairs, which does ON keep?

Question 5

Say Instagram's comments table has 6 rows, and each one holds the user_id of an account in its users table, which has 10 rows. Its database gets SELECT users.username, comments.text FROM comments JOIN users ON comments.user_id = users.id;.

How many rows come back in the result?

rows

Question 6

Simplified's users table has six rows: Ada (id 1), Ben (2), Cy (3), Dee (4), Eli (5) and Fay (6). One row of attempts has id 3 and user_id 2. By mistake, a query writes SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.id = users.id;.

Which name is attempt 3 paired with in the result?

Filtering the joined rows

SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.id;attemptsiduser_idlesson_id111212321413541622741usersidnameemail1Adaada@simplified.org2Benben@simplified.org3Cycy@simplified.org4Deedee@simplified.org5Elieli@simplified.org6Fayfay@simplified.orgresultnamelesson_idAda1Ada2Ben1Ada3Dee1Ben2Dee1
SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.idWHERE users.email = 'ada@simplified.org';attemptsiduser_idlesson_id111212321413541622741usersidnameemail1Adaada@simplified.org2Benben@simplified.org3Cycy@simplified.org4Deedee@simplified.org5Elieli@simplified.org6Fayfay@simplified.orgresultcount3
SELECT posts.captionFROM postsJOIN accounts ON posts.account_id = accounts.idWHERE accounts.username = 'maya.dev';postsidaccount_idcaption79205Gym day80377New desk81377Beach day83377Coffee runaccountsidusername205sam.codes377maya.devresultcaptionNew deskBeach dayCoffee run
SELECT users.name, attempts.lesson_idFROM attemptsJOIN usersON attempts.user_id = users.idWHERE users.email = 'ada@simplified.org';attemptsiduser_idlesson_id111212321413541622741usersidnameemail1Adaada@simplified.org2Benben@simplified.org3Cycy@simplified.org4Deedee@simplified.org5Elieli@simplified.org6Fayfay@simplified.orgresultnamelesson_idAda1Ada2Ada3

Databases · Lesson 4.3

Reading Across Tables

Filtering the joined rows

Simplified
0:00 / 2:14

So the join sent back 7 rows, one for each attempt, each with the learner's name.

Ada's name came back three times, because three attempts hold her id, 1.

SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.id;attemptsiduser_idlesson_id111212321413541622741usersidname1Ada2Ben3Cy4Dee5Eli6Fayresultnamelesson_idAda1Ada2Ben1Ada3Dee1Ben2Dee1

Now say the team wants just Ada's answers on the quiz analytics page.

They look her up by the email she logs in with, ada@simplified.org.

But attempts has no email column, so how does the query pick out Ada's attempts?

Her email is in users, and the join puts users' columns beside each attempt.

So the query keeps the join, and adds a WHERE after ON: users.email equals ada@simplified.org.

A WHERE after JOIN … ON picks which joined rows come back.

It can check a column of either table, because each joined row holds both.

SELECT users.name, attempts.lesson_idFROM attemptsJOIN users ON attempts.user_id = users.idWHERE users.email = 'ada@simplified.org';attemptsiduser_idlesson_id111212321413541622741usersidnameemail1Adaada@simplified.org2Benben@simplified.org3Cycy@simplified.org4Deedee@simplified.org5Elieli@simplified.org6Fayfay@simplified.orgresultnamelesson_idAda1Ada2Ada3

First, ON matches the rows up: each attempt keeps its one learner, so 7 joined rows.

Then WHERE checks users.email in each joined row.

Attempts 1, 2 and 4 are joined with Ada, so their email is ada@simplified.org: they pass.

Attempts 3 and 6 are joined with Ben, and 5 and 7 with Dee, so they fail.

So the result holds Ada's 3 attempts, at lessons 1, 2 and 3.

So how many answers has Ada given?

The query writes COUNT star where the columns go, just as it did to count users.

SELECT COUNT(*)FROM attemptsJOIN users ON attempts.user_id = users.idWHERE users.email = 'ada@simplified.org';attemptsiduser_idlesson_id111212321413541622741usersidnameemail1Adaada@simplified.org2Benben@simplified.org3Cycy@simplified.org4Deedee@simplified.org5Elieli@simplified.org6Fayfay@simplified.orgresultcount3

COUNT star counts the joined rows that pass WHERE.

Three pass, so Postgres sends back one row, holding 3.

So how might Instagram show maya.dev's posts on her profile?

You open instagram.com/maya.dev, so Instagram knows her username, not her id.

Say each row of its posts table holds the account ID of the account that posted it.

SELECT posts.captionFROM postsJOIN accounts ON posts.account_id = accounts.idWHERE accounts.username = 'maya.dev';postsidaccount_idcaption79205Gym day80377New desk81377Beach day83377Coffee runaccountsidusername205sam.codes377maya.devresultcaptionNew deskBeach dayCoffee run

So Instagram might JOIN posts to accounts, ON posts.account ID equals accounts.id.

Then WHERE accounts.username equals maya.dev keeps the posts joined with her row.

So her profile shows her three posts, New desk, Beach day and Coffee run, and no one else's.

ON matches the rows of two tables up, and WHERE then picks which matches come back.

Example: Filtering the joined rows

Simplified's attempts table has 7 rows. Their user_id values are 1, 1, 2, 1, 4, 2 and 4, and their lesson_id values are 1, 2, 1, 3, 1, 2 and 1. Its users table has six rows, with ids 1 to 6: Ada, Ben, Cy, Dee, Eli and Fay. Its server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id WHERE attempts.lesson_id = 2;.

How many rows come back in the result?

Explanation

First, ON keeps one pair for each attempt, so there are 7 joined rows.

Then, WHERE keeps the joined rows whose attempts.lesson_id is 2: the second attempt, Ada's, and the sixth, Ben's.

Therefore, 2 rows come back.

Question 1

Simplified's attempts table has 7 rows, with user_id values 1, 1, 2, 1, 4, 2 and 4. Its users table has six rows, with ids 1 to 6, and Dee's row has id 4 and email dee@simplified.org. Its server sends Postgres SELECT COUNT(*) FROM attempts JOIN users ON attempts.user_id = users.id WHERE users.email = 'dee@simplified.org';.

What number comes back?

Question 2

Say Spotify's songs table has the columns id, title and album_id, and its albums table has id and name. A query starts SELECT songs.title FROM songs JOIN albums ON songs.album_id = albums.id. It should send back only the songs on the album Hit Me Hard and Soft.

Which line should come next?

Question 3

Say Uber's trips table has 5 rows, with rider_id values 3, 7, 3, 3 and 9, and each trip's driver_id matches one row of its drivers table. Its database gets SELECT trips.id, drivers.name FROM trips JOIN drivers ON trips.driver_id = drivers.id WHERE trips.rider_id = 3;.

How many rows come back?

rows

Question 4

Simplified's server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id WHERE users.name = 'Ben';.

What do ON and WHERE each do here?

Question 5

Simplified's users table has six rows: Ada (id 1), Ben (2), Cy (3), Dee (4), Eli (5) and Fay (6). Its attempts table has 7 rows, with user_id values 1, 1, 2, 1, 4, 2 and 4. Its server sends Postgres SELECT users.name, attempts.lesson_id FROM attempts JOIN users ON attempts.user_id = users.id WHERE users.name = 'Cy';.

How many rows come back?

rows

Question 6

Simplified's attempts table has 7 rows, with user_id values 1, 1, 2, 1, 4, 2 and 4. Its users table has six rows, and Ada's row has id 1 and email ada@simplified.org. Its server sends Postgres SELECT COUNT(*) FROM attempts JOIN users ON attempts.user_id = users.id WHERE users.email = 'ada@simplified.org';.

What comes back?

Assistant

Highlight lesson text and click Ask AI, or type a question below.