0

I am doing a query with a trivial join, with the intention of finding the newest record that 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 - and 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.

Winfield Trail
  • 5,535
  • 2
  • 27
  • 43
  • Where do `status` and `created` come from? Please clarify and (always) add your version of Postgres. – Erwin Brandstetter May 12 '14 at 20:38
  • 1
    This question may provide some insight: http://stackoverflow.com/questions/5658457/not-equal-operator-in-t-sql-on-null Bottom line where one of the operators is NULL makes the expression evaluate to FALSE – Bob Kaufman May 12 '14 at 20:42
  • I think Bob's got it right here - since I'm doing a != comparison it would react to a NULL on one side in the way he suggests, which i failed to consider. – Winfield Trail May 12 '14 at 20:42
  • `status` and `created` come from `versions`, but aren't relevant - as I said, the query behaves as intended if I test for NULL via OR. – Winfield Trail May 12 '14 at 20:45
  • 1
    @sudowned: It is *always* relevant where each and every column comes from. Even if those columns do not seem relevant to you. This *needs* to be clear in the question. – Erwin Brandstetter May 12 '14 at 20:48
  • @ErwinBrandstetter I disagree. The issue was already isolated specifically to the second `AND` clause - I could have omitted mention of those columns entirely from my query and still obtained an accurate answer. If anything, that only indicates that I *should* have removed those columns from the query... which I have now done. – Winfield Trail May 13 '14 at 02:35
  • That works, too. Even if `LIMIT 1` without `ORDER BY` might arouse skeptics. – Erwin Brandstetter May 13 '14 at 02:54
  • Does this answer your question? [Not equal <> != operator on NULL](https://stackoverflow.com/questions/5658457/not-equal-operator-on-null) – philipxy May 17 '22 at 01:22

1 Answers1

3

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228