Simplified
Simplified

L3.1Greater than and less than

YOU

Greater than and less than

SELECT name, xp FROM usersWHERExp=40;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpBen40Eli40Ben40Eli40?
SELECT name, xp FROM usersWHERExp=40;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpmore than 100 XP101120300…?
SELECT name, xp FROM usersWHERExp>100;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Dee300Ada120Dee300?
SELECT id, hour FROM flightsWHEREhour<9;flightsidhour162839413idhour162839413✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultidhour16281628?
SELECT name, xp FROM usersWHERExp>120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpDee300Dee300?

Databases · Lesson 3.1

Comparing Values

Greater than and less than

Simplified
0:00 / 1:56

So on Simplified's analytics screen, WHERE XP equals 40 kept Ben's and Eli's rows.

Because equals passes a row only when its value is exactly 40.

Now say the screen lists the learners with more than 100 XP.

That's 101, 120, 300, or any number above 100, not one exact number.

SELECT name, xp FROM usersWHERExp=40;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpmore than 100 XP101120300…

So how does a condition keep every row over 100?

It uses greater than, written like this: >.

Greater than passes a row when its value is over the number.

The query ends with WHERE XP greater than 100.

SELECT name, xp FROM usersWHERExp>100;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Dee300Ada120Dee300

Postgres checks each row's XP against 100.

Ada's 120 is over 100, so her row passes.

Ben's 40 isn't, so his row fails.

Then Cy's 0 fails, Dee's 300 passes, Eli's 40 fails, and Fay's 75 fails.

So the result has two rows: Ada and Dee.

And what if Google Flights lists only the flights that leave before 9 am?

SELECT id, hour FROM flightsWHEREhour<9;flightsidhour162839413idhour162839413✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultidhour16281628

Say it keeps each flight's departure hour as a number, from 0 to 23.

It uses less than, written like this: <.

Less than passes a row when its value is under the number.

So the query ends with WHERE hour less than 9.

The flights at 6 and 8 leave before 9, so their rows pass.

The 9 am flight fails, because 9 isn't less than 9.

And the 1 pm flight, at hour 13, fails too.

And on the analytics screen, what does WHERE XP greater than 120 do to Ada, at exactly 120?

SELECT name, xp FROM usersWHERExp>120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpDee300Dee300

Ada's 120 isn't greater than 120: it's equal to it.

So Ada's row fails.

Only Dee's 300 is over 120, so the result has one row: Dee.

So greater than and less than keep the values past the number, and the number itself fails.

Example: Greater than and less than

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 > 50;.

How many rows pass the condition?

Explanation

First, xp > 50 passes a row only if its xp is over 50.

Then, Ada's 120, Dee's 300 and Fay's 75 are over 50; the other three aren't.

Therefore, 3 rows pass: Ada, Dee and Fay.

Question 1

Say Spotify's server sends its Postgres database SELECT title FROM songs WHERE plays > 1000;. One song has exactly 1,000 plays.

Does it come back?

Greater than or equal to

SELECT name, xp FROM usersWHERExp>120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpDee300Dee300?
SELECT name, xp FROM usersWHERExp>=120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Dee300Ada120Dee300?
SELECT id, price FROM casesWHEREprice<=20;casesidprice115220335idprice115220335✗✗✗?unknown?unknown?unknown0 rowsresultidprice115220115220?
usersnamexpAda1201201xp>=120Ada ✗Dee2xp>=120Ada ✓Dee3xp<=120Ada ✗BenCyEliFay4xp<=120Ada ✓BenCyEliFay?

Databases · Lesson 3.2

Comparing Values

Greater than or equal to

Simplified
0:00 / 1:39

So on Simplified's analytics screen, WHERE XP greater than 120 kept only Dee.

Ada's row failed, because her 120 is equal to 120, not greater.

Now say the screen lists the learners with 120 XP or more, so Ada must show.

So how does a condition let 120 itself pass too?

It adds an equals sign, right after the greater than sign.

That's called greater than or equal to, written like this: >=.

Greater than or equal to passes a row when its value is over the number, or equal to it.

SELECT name, xp FROM usersWHERExp>=120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Dee300Ada120Dee300

Ada's 120 is equal to 120, so her row passes now.

Dee's 300 is greater, so hers passes too.

Ben's, Cy's, Eli's and Fay's are under 120, so they fail.

So the result has two rows: Ada and Dee.

And what if a shopper on Amazon filters for phone cases at $20 or less?

Amazon's query might use less than or equal to, written like this: <=.

So it ends with WHERE price less than or equal to 20.

SELECT id, price FROM casesWHEREprice<=20;casesidprice115220335idprice115220335✗✗✗?unknown?unknown?unknown0 rowsresultidprice115220115220

The case at 15 dollars is under 20, so it passes.

The one at 20 is equal to 20, so it passes too.

And the one at 35 is over 20, so it fails.

So how does Ada's 120 do against all four, with and without the equals sign?

usersnamexpAda1201201xp>=120Ada ✗Dee2xp>=120Ada ✓Dee3xp<=120Ada ✗BenCyEliFay4xp<=120Ada ✓BenCyEliFay

She fails greater than 120, but passes greater than or equal to 120.

And she fails less than 120, but passes less than or equal to 120.

So the equals sign lets a value equal to the number pass too.

Example: Greater than or equal to

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 >= 40;.

How many rows pass the condition?

Explanation

First, >= lets 40 itself pass, as well as every xp over 40.

Then, Ada, Ben, Dee, Eli and Fay pass; only Cy's 0 fails.

Therefore, 5 rows pass.

Question 1

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 does the result have?

rows

Question 2

Say YouTube keeps videos in a Postgres table with a minutes column: each video's length, in whole minutes. A viewer filters for videos 10 minutes long or more, and a video of exactly 10 minutes must show.

Which condition does YouTube's query need?

Question 3

Say YouTube keeps one channel's videos in a Postgres table called videos, with a views column. The channel's five videos have 10, 250, 0, 250 and 9,000 views. YouTube's server sends SELECT title FROM videos WHERE views <= 250;.

How many rows does the result have?

rows

Question 4

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75. Its analytics screen lists the learners with 300 XP or more.

Which condition does the query need?

Question 5

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 does the result have?

rows

Question 6

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.

How many more rows does WHERE xp >= 40 keep than WHERE xp > 40?

rows

Not equal to

SELECT name, xp FROM usersWHERExp>=120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Dee300Ada120Dee300?
SELECT name, xp FROM usersWHERExp>=120;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexp?
SELECT name, xp FROM usersWHEREemail<>'ben@simplified.org';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Eli40Fay75Ada120Cy0Dee300Eli40Fay75?
SELECT name, xp FROM usersWHERExp<>40;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Fay75Ada120Cy0Dee300Fay75?
SELECT id, views FROM videosWHEREviews<>0;videosidviews11020325040idviews11020325040✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultidviews11032501103250?

Databases · Lesson 3.3

Comparing Values

Not equal to

Simplified
0:00 / 1:26

So on Simplified's analytics screen, WHERE XP greater than or equal to 120 kept Ada and Dee.

Now say the screen lists every learner except Ben.

So how does a condition keep every row but one?

It uses not equal to, written like this: <>.

Not equal to passes a row when its value isn't the one you give.

The query ends with WHERE email not equal to Ben's email.

Ben's email goes in single quotes, because it's text.

SELECT name, xp FROM usersWHEREemail<>'ben@simplified.org';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Eli40Fay75Ada120Cy0Dee300Eli40Fay75

Postgres checks each row's email against Ben's.

Ada's email is different, so her row passes.

Ben's row has that exact email, so it fails.

Cy's, Dee's, Eli's and Fay's emails are different, so their rows pass too.

So the result has five rows: everyone but Ben.

And how does not equal to work on a number, like WHERE XP not equal to 40?

SELECT name, xp FROM usersWHERExp<>40;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Fay75Ada120Cy0Dee300Fay75

Ben's 40 and Eli's 40 are equal to 40, so their rows fail.

Ada's, Cy's, Dee's and Fay's are not, so four rows pass.

And what if YouTube lists a channel's videos, except those with 0 views?

Its query might end with WHERE views not equal to 0.

SELECT id, views FROM videosWHEREviews<>0;videosidviews11020325040idviews11020325040✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultidviews11032501103250

The two videos with 0 views are equal to 0, so they fail.

The videos with 10 and 250 views pass: two rows.

So not equal to fails the rows equal to the value, and passes the rows with any other value.

Example: Not equal to

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 <> 'Cy';.

How many rows pass the condition?

Explanation

First, <> fails only the rows whose name is exactly Cy.

Then, Cy's row fails, and the other five names aren't Cy.

Therefore, 5 rows pass.

Question 1

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 does the result have?

rows

Question 2

Say Instagram keeps one account's posts in a Postgres table called posts, with a likes column. Its five posts have 12, 0, 12, 7 and 12 likes. Instagram's server sends SELECT id FROM posts WHERE likes <> 12;.

How many rows does the result have?

rows

Question 3

Simplified's users table holds six learners: Ada with 120 XP, Ben 40, Cy 0, Dee 300, Eli 40 and Fay 75.

Which condition keeps every learner except Dee?

Question 4

Say Spotify keeps songs in a Postgres table called songs, with an artist column that has NOT NULL, so every song has an artist. Spotify's server sends SELECT title FROM songs WHERE artist <> 'Drake';.

Which songs come back?

Question 5

Simplified's users table holds six learners, with the emails ada@simplified.org, ben@simplified.org, cy@simplified.org, dee@simplified.org, eli@simplified.org and fay@simplified.org. Its server sends Postgres SELECT name FROM users WHERE email <> 'zoe@simplified.org';.

How many rows does the result have?

rows

Question 6

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 SELECT name FROM users WHERE xp = 40;, then SELECT name FROM users WHERE xp <> 40;.

How many rows do the two results hold together?

rows

Why equals can't find NULL

SELECT name, xp FROM usersWHEREemail<>'ben@simplified.org';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-11✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Eli40Fay75Ada120Cy0Dee300Eli40Fay75?
SELECT name, xp FROM usersWHEREname<>'Ben';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.org02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.org02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexp?
SELECT name, xp FROM usersWHEREname<>'Ben';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Eli40Fay75Ada120Cy0Dee300Eli40Fay75?
SELECT email FROM usersWHEREname=NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultemail?
SELECT email FROM usersWHEREnameIS NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultemailivy@simplified.orgivy@simplified.org?
SELECT name FROM usersWHEREnameIS NOT NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnameAdaBenCyDeeEliFayAdaBenCyDeeEliFay?
SELECT id FROM messagesWHEREread_atIS NULL;messagesidtext1Hi!2Lunch?3Call me4You there?idtext1Hi!2Lunch?3Call me4You there?✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultid3434?

Databases · Lesson 3.4

Comparing Values

Why equals can't find NULL

Simplified
0:00 / 2:10

So WHERE email not equal to Ben's email kept every learner but Ben.

Now say a new learner, Ivy, signs up with just her email.

She skips the optional name, so Postgres stores NULL in her name cell: no value at all.

SELECT name, xp FROM usersWHEREname<>'Ben';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexp

Then the screen lists every learner except Ben again, this time by name.

So its query ends with WHERE name not equal to Ben.

Six learners aren't Ben, but only five rows come back: Ivy's is missing.

So why was Ivy's row left out, when her name isn't Ben?

SELECT name, xp FROM usersWHEREname<>'Ben';usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnamexpAda120Cy0Dee300Eli40Fay75Ada120Cy0Dee300Eli40Fay75

Ivy's cell holds no value, so there's nothing to compare with Ben.

So Postgres can't say she's Ben, and it can't say she isn't.

That result is called unknown.

Unknown is what a comparison with NULL gives: Postgres can't tell if it's a match.

And WHERE keeps only the rows that pass, so Ivy's row is left out.

So can WHERE name equals NULL find her?

SELECT email FROM usersWHEREname=NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultemail

No: that's a comparison with NULL too, so every row is unknown.

Even Ivy's: Postgres can't tell if two missing values match.

So no row passes, and the result is empty.

So how does a condition ask for the rows with no value?

It uses IS NULL.

IS NULL passes a row when its cell holds no value.

SELECT email FROM usersWHEREnameIS NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultemailivy@simplified.orgivy@simplified.org

It compares nothing: it only checks whether a value is there.

Ada to Fay each have a name, so their six rows fail.

Ivy's cell holds no value, so her row passes.

So one row comes back: ivy@simplified.org.

The opposite is IS NOT NULL.

IS NOT NULL passes a row when its cell does hold a value.

SELECT name FROM usersWHEREnameIS NOT NULL;usersidemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02idemailnamexpcreated_at1ada@simplified.orgAda1202026-01-052ben@simplified.orgBen402026-01-093cy@simplified.orgCy02026-02-144dee@simplified.orgDee3002026-03-025eli@simplified.orgEli402026-03-206fay@simplified.orgFay752026-04-117ivy@simplified.orgNULL02026-05-02✗✗✗✗✗✗✗?unknown?unknown?unknown?unknown?unknown?unknown?unknown0 rowsresultnameAdaBenCyDeeEliFayAdaBenCyDeeEliFay

So Ada to Fay pass, and Ivy's row fails.

Now how might WhatsApp find the messages you haven't read?

Say it keeps the time you read each message in a read at column.

SELECT id FROM messagesWHEREread_atIS NULL;messagesidtextread_at1Hi!09:022Lunch?09:153Call meNULL4You there?NULLidtextread_at1Hi!09:022Lunch?09:153Call meNULL4You there?NULL✗✗✗✗?unknown?unknown?unknown?unknown0 rowsresultid3434

A message you haven't opened has no time yet, so its cell holds NULL.

So its query might end with WHERE read at IS NULL.

Two messages have no read time, so two rows pass: 2 unread.

So a comparison never passes a NULL: only IS NULL finds it.

Example: Why equals can't find NULL

Simplified's users table holds seven learners. Six have a name: Ada, Ben, Cy, Dee, Eli and Fay. The seventh skipped the optional name, so her name cell is NULL.

Its server sends Postgres SELECT email FROM users WHERE name <> 'Ada';.

How many rows come back in the result?

Explanation

First, Ada's row fails, because her name is equal to Ada.

Then, the NULL name against Ada is unknown, so that row is left out too.

Therefore, 5 rows come back: Ben's, Cy's, Dee's, Eli's and Fay's.

Question 1

Simplified's users table holds eight learners. Five gave a name when they signed up, and three skipped the optional name, so their name cells are NULL. Its server sends Postgres SELECT email FROM users WHERE name IS NULL;.

How many rows does the result have?

rows

Question 2

Say Instagram keeps accounts in a Postgres table called accounts, with a bio column. An account that never wrote a bio has NULL in its bio cell. Instagram wants the usernames of the accounts with no bio.

Which condition finds them?

Question 3

Say WhatsApp keeps one chat's messages in a Postgres table called messages, with a read_at column for the time you read each one. The chat has 10 messages, and 4 of them have NULL in read_at, because you haven't read them. WhatsApp's server sends SELECT id FROM messages WHERE read_at IS NOT NULL;.

How many rows does the result have?

rows

Question 4

Simplified's users table holds seven learners. Five have a name, and two skipped the optional name, so their name cells are NULL. Its server sends Postgres SELECT email FROM users WHERE name = NULL;.

How many rows does the result have?

rows

Question 5

Say Instagram's accounts table holds three accounts. The bio of maya.dev is 'Coffee first', the bio of sam.codes is 'Learning to code', and the bio cell of leo.builds is NULL. Instagram's server sends SELECT username FROM accounts WHERE bio <> 'Coffee first';.

Which usernames come back?

Question 6

Say Spotify keeps songs in a Postgres table called songs, with an album column. The table holds 12 songs. 5 of them are singles with no album, so their album cells are NULL. Spotify's server sends SELECT title FROM songs WHERE album IS NULL;, then SELECT title FROM songs WHERE album IS NOT NULL;.

How many rows do the two results hold together?

rows
Assistant

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