sqlpostgresqlnullleft-join

Why does null seem to equal an integer in WHERE?

I am doing a query with a trivial join, with the intention of finding the newest row the user hasn't voted on yet:

SELECT
    v.user_id,
    v.version_id,
    vv.user_id
FROM versions v
LEFT JOIN versions_votes vv ON v.version_id = vv.version_id
WHERE vv.user_id != 39;

Curiously, this returns no rows if vv.user_id is null. My admittedly pedestrian understanding of the problem is that NULL cannot be equal to anything - that's why we have to test for IS NULL rather than = NULL in the first place.

And yet, here we are. If I modify the WHERE clause as follows:

WHERE (vv.user_id != 39 OR vv.user_id IS NULL)

the query appears to work properly (and also appears to confirm that NULL is evaluating to 39.

Solution

  • You are right that "NULL cannot be equal to anything".
    What you are missing is that NULL cannot be unequal, either.

    NULL compared to anything is always NULL. The problem at hand is that you got the LEFT JOIN wrong. This should work:

    SELECT v.user_id, v.version_id, vv.user_id
FROM   versions v
LEFT   JOIN versions_votes vv ON v.version_id = vv.version_id
                             AND vv.user_id = 39
WHERE  vv.version_id IS NULL
ORDER  BY v.created
LIMIT  1;

    You had an additional condition referencing vv in the WHERE clause: AND vv.user_id != 39. Probably expecting that NULL != 39 would qualify, but it doesn't. See:

    There are basically three different techniques to do this: