Question 229622 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL left join with where clause leaving out the empty field

I have 2 tables. A Users table and a Token table. I am trying to join these tables to see who has verified their tokens and who hasn’t. The token table has a foreign key with the user id in it.

Right now I am doing a left join on the user ids and trying to check if there is no verification. It looks like this:

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

So if I have the following 2 tables like so:

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

Then

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

As you can see Joe does not have a record in the second table. I want to join the tables and even though Joe doesn’t have a record in the I would like that to basically count as false/null. When I run my query searching for unverified users I get the following data with only 2 entries.

Name  | Verified
Sally | 0
Dave  | 0

If I take out the where clause I get something like the following:

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

As you can see Joe is at the bottom with a null value but I would like to count this somehow as being unverified. How would I write a query that would result in me getting the following data.

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

Or even just the following to result in a count of 3. Right now it would come back as a count of 2.

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

5

Answers


  1. 0 
    WHERE verified = false or verified is null
    Login or Signup to reply.
  2. 0

    AS verified is in tokens table your query should be as follows…

    SELECT a.name, b.verified FROM users as a LEFT JOIN 
(SELECT UID, verified FROM tokens 
WHERE verified = false) as b ON a.ID = b.UID
    Login or Signup to reply.
  3. 0

    You are so very close to what you need! COALESCE takes a list of variable/values and will output the first non-null result.

    So why do we need COALESCE? When doing a left join usually we will show all values even those without a match. However in your WHERE clause you have added WHERE verified = false

    Verified is a field unique to the tokens table and so when we put that condition it essentially removes our LEFT Join as we must now match the condition.

    This is when our COALESCE comes into play. If we were to type COALESCE(verified, false) then if verified was NULL we would get false instead. This would make the WHERE clause
    WHERE COALESCE(verified, false) = false

    EDIT: I just reread your question and might also suggest using COALESCE as part of the SELECT itself. COALESCE can be used in either WHERE or SELECT and in if you use it in the SELECT then your Null verified result will display as false.

    Now this isn’t the only solution to your problem

    If you instead desired that we have NULL displayed then we must modify our WHERE to include NULL as an option. To do so simply add an OR verified IS NULL so that you have WHERE verified = false OR verified IS NULL

    Login or Signup to reply.
  4. 0

    WHERE clauses are applied after the JOINs are completed, by which time the values are potentially NULL already.

    What you need can be rephrased as where there ISN'T a token with verified = 1 (Known as an anti-join.)

    This can be achieved with NOT EXISTS, which (unlike OR or COALESCE) allows maximal use of indexes.

    SELECT COUNT(*)
  FROM users
 WHERE NOT EXISTS (
   SELECT * 
     FROM tokens 
    WHERE tokens.UID = users.ID
      AND tokens.verified = true
 )
    Login or Signup to reply.
  5. 0

    If you want to list all users and see if they are verified or not, use query:

    SELECT name, ifnull(verified,0) verified
FROM users 
  LEFT JOIN tokens ON users.ID = tokens.UID;

    If you want to have only non-verified users, use:

    SELECT name, verified
FROM (
 SELECT name, ifnull(verified,0) verified
 FROM users 
   LEFT JOIN tokens ON users.ID = tokens.UID
) AS q
WHERE verified=0;

    See db-fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search