Q:

WHERE clause in LEFT JOIN query

So i wish to get all the the SSC positions and get the number of positions which are not yet occupied. However, i somehow got stuck in the where clause.
Here is my table structures:

And here is my query
SELECT p.position_id, a.account_id 
            FROM positions p 
            LEFT JOIN ssc s ON s.position_id = p.position_id AND s.removed = 0
            LEFT JOIN students stud ON stud.students_id = s.students_id
            LEFT JOIN accounts a ON a.account_id = stud.account_id
            WHERE p.user_level_id = 6 
            AND p.removed = 0
The actual result is this:

Expected result to show only the fields which are NULL in account_id. However if I include AND a.account_id = NULL in the query, the result is empty. And if i ever change the query into:
SELECT p.position_id, a.account_id 
            FROM positions p 
            LEFT JOIN ssc s ON s.position_id = p.position_id AND s.removed = 0
            LEFT JOIN students stud ON stud.students_id = s.students_id
            LEFT JOIN accounts a ON a.account_id = stud.account_id AND a.account_id = NULL
            WHERE p.user_level_id = 6 
            AND p.removed = 0
Then all the account ID becomes NULL

A:

None of them = the NULL value. Use IS NULL.
a.account_id IS NULL

You can read more about this here, https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html. You may prefer to use empty strings, or a default value of 0.

