Databases · Lesson 2.1
Finding one row among millions
So SELECT name, email FROM users sent back every learner.
That's 2,000,000 rows, one for each learner.
Now say you're Ada, and you open your Learn page on Simplified.
It shows your name and your XP, so Simplified's server asks Postgres for them.
But SELECT name, XP FROM users would send back all 2,000,000 learners.
And the server needs just one row: yours.
So how does it ask for just Ada's row, and not the other 1,999,999?
It adds a condition to the query, after WHERE.
WHERE gives a condition each row must pass, and only the rows that pass come back.
Since you logged in, Simplified's server knows your row's ID: 1.
So the query ends with WHERE ID equals 1.
Equals means a row passes only if its ID is exactly 1.
Postgres checks the condition on each row of users, one at a time.
Ada's row has the ID 1, so it passes.
Ben's row has the ID 2, so it fails, and so does every other row.
So one row comes back: Ada, 120, the XP on your Learn page.
And what if Simplified's analytics page lists the learners with exactly 40 XP?
Then the query ends with WHERE XP equals 40.
Postgres checks each row's XP against 40.
Ben's row has 40, so it passes, and so does Eli's.
Every other row fails.
So the result has two rows: Ben and Eli.
And what if the page lists learners with no XP yet?
Then the query ends with WHERE XP equals 0.
Only Cy's row has 0, so it passes, and every other row fails.
So one row comes back: Cy, with 0.
So SELECT picks the columns, and WHERE picks the rows.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp = 75;.
How many rows come back in the result?
Explanation
First, WHERE keeps only the rows whose xp is exactly 75.
Then, only Fay's row has 75; the other five don't.
Therefore, 1 row comes back: Fay.
Simplified's users table has columns id, email, name, xp and created_at, and every learner's id is different. Dee's id is 4.
Which query sends back just Dee's name?
Databases · Lesson 2.2
Why text needs quotes
So WHERE ID equals 1 kept just Ada's row.
And WHERE XP equals 40 kept Ben's and Eli's.
Each condition compared a column with a number.
Now say Ada types her email on Simplified's login page.
The server has her email, not her ID, so the condition must compare text.
So how does a condition compare a column with text, like an email?
It starts the same: WHERE email equals ada@simplified.org.
And the email goes in single quotes.
Single quotes mark where a piece of text starts and ends.
So Postgres reads the whole email as one value.
Postgres checks each row's email against ada@simplified.org.
Ada's row has exactly that email, so it passes.
Every other row has a different email, so it fails.
So one row comes back: Ada, 120.
And what if a mentor asks Simplified's team for Cy's email?
Then the query ends with WHERE name equals Cy, in single quotes.
Because a name is text too.
Only Cy's row has the name Cy, so it passes.
So one row comes back: cy@simplified.org.
So does every value in a condition get quotes?
WHERE name equals Ben puts Ben in quotes, because a name is text.
And WHERE XP equals 40 has no quotes, because 40 is a number.
So text in a condition goes in single quotes, and numbers don't.
Simplified's server needs the name of the learner whose email is fay@simplified.org.
How should the condition be written?
Explanation
First, the condition compares the email column with fay@simplified.org.
Then, an email is text, so it goes in single quotes.
Therefore, the condition is WHERE email = 'fay@simplified.org'.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT xp FROM users WHERE name = 'Eli';.
What does the result hold?
Simplified's users table has columns id, email, name, xp and created_at, and every learner's email is different. Ben signed up with ben@simplified.org.
Which query sends back just Ben's name?
Simplified's server sends Postgres SELECT name FROM users WHERE email = 'ben@simplified.org';.
Why is ben@simplified.org in single quotes?
Simplified's server needs the row of the learner named Dee.
Which condition is written correctly?
Simplified's server sends Postgres SELECT name FROM users WHERE xp = 300;.
Why is 300 not in quotes?
In a condition like WHERE name = 'Ada', what kind of quotes go round the text?
Databases · Lesson 2.3
When no row passes
So the query for ada@simplified.org sent back one row: Ada's.
Her row passed, because she signed up with that email.
Now say someone types zoe@simplified.org on Simplified's login page.
But nobody signed up with that email.
So what does Postgres send back when no row passes?
The query ends with WHERE email equals zoe@simplified.org, in single quotes.
Postgres checks every row, and none of them has that email.
So it sends back a result with its columns, name and XP, and 0 rows.
That's called an empty result.
An empty result is a result with 0 rows: the query worked, and no row passed.
It's not an error: it tells the server nobody signed up with that email.
And what if the analytics page lists the learners with exactly 50 XP?
Then the query ends with WHERE XP equals 50.
No learner has exactly 50: Fay has 75, and Ben and Eli have 40.
So it sends back an empty result: the name column, with 0 rows.
And what if a mentor asks about a learner named Gus?
Then the query ends with WHERE name equals Gus, in single quotes.
No row in users has the name Gus.
So it sends back an empty result: email and XP, with 0 rows.
So when no row passes, the result has its columns and 0 rows.
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Its server sends Postgres SELECT name FROM users WHERE xp = 100;.
How many rows come back in the result?
Explanation
First, WHERE keeps only the rows whose xp is exactly 100.
Then, no learner has exactly 100, so every row fails.
Therefore, 0 rows come back: an empty result.
Say Spotify keeps its songs in a Postgres table with an artist column. Spotify's server sends SELECT title FROM songs WHERE artist = 'Zed Ravel';, and no song has that artist.
What does Postgres send back?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its server sends Postgres SELECT name, xp FROM users WHERE xp = 60;.
How many rows does the result have?
Simplified's users table holds 2,000,000 learners. Its server sends Postgres SELECT name FROM users WHERE email = 'kim@simplified.org';, and gets back a result with 0 rows.
What does that tell the server?
Simplified's users table holds six learners, and the highest XP is 300. Its server sends Postgres SELECT name, email FROM users WHERE xp = 999;.
How many columns does the result have?
Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.
Which query sends back an empty result?
No row passes a query's WHERE, so Postgres sends back a result with its columns and 0 rows.
What is that called?