SQL left join using where clause to filter empty fields

Question

I have two tables, one is the Users table and the other is the Token table. I want to join these two tables to see which users have verified their tokens and which have not. There is a foreign key in the token table that contains the user ID.

Now I'm using a left join to join the user IDs and trying to check if there is no validation. The query statement is as follows:

SELECT name, verified
FROM users LEFT JOIN tokens
ON users.ID = tokens.UID
WHERE verified = false

Suppose I have the following two tables:

ID | Name
0 | Joe
1 | Sally
2 | Dave
3 | John

Then, the token table is as follows:

ID | UID | Verifed
0 | 1 | 0
1 | 2 | 0
2 | 3 | 1

As you can see, Joe has no records in the second table. I want to join these two tables and even if Joe has no record in the second table, I want it to be treated as false/null. When I run the query to find unauthenticated users, only 2 entries of data are returned.

Name | Verified
Sally | 0
Dave | 0

If I remove the where clause, the result will be as follows:

Name | Verified
Sally | 0
Dave | 0
John | 1
Joe | NULL

As you can see, Joe has a null value at the bottom, but I want it to be treated as unvalidated. How should I write a query to get the following data:

Name | Verified
Sally | 0
Dave | 0
Joe | NULL

Or just the following query statement, the result is 3. The current query result is 2.

SELECT count(*)
FROM users LEFT JOIN tokens
ON users.ID = tokens.UID
WHERE verified = false

Answer 1

WHERE verified = false 或 verified is null