SQL IS NOT NULL not working

user_5383071 0
Viewed 15899

I am attempting to write a short SQL query (in MySQL Workbench) that outputs the number of fails in a subject if it has been failed by more than one student.

Here's my attempt:

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 AND result <> NULL
GROUP BY code
HAVING Count(Student) > 1;

The problem is it keeps outputting the count which contains the Null record even though I have specified in the query to not count them (or at least I thought I did...).

EDIT : It was the GROUP BY clause that went wrong! As Gordon Linoff has pointed out in his answer, it should have been GROUP BY SubjectCode and that magically solved the problem.

Thank you all for the constructive insights.

mysql-workbench mysql sql
3 Answers

As written:

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 AND result <> NULL
GROUP BY code
HAVING Count(Student) > 1;

This query should return no rows. Why? result <> NULL returns NULL as a boolean value (to a close approximation all comparisons to NULL return NULL). AND NULL evaluates NULL -- and NULL is not true. All rows are filtered out.

The NULL comparison is actually superfluous. The result < 50 will also filter out NULL values.

Looking at the rest of the query, you have another issue. The GROUP BY is on code. It should really be on SubjectCode -- the result of the CONCAT(). In fact, when concatenating different columns, I would recommend using a separator, say CONCAT_WS(':', area, yearlevel, code). Of course a separator may not be desirable for this particular situation.

user_11440350

result NOT NULL instead of result <> NULL.

SELECT CONCAT(area, yearlevel, code) AS SubjectCode, Count(student)
FROM StudentTakesSubject
WHERE result < 50 
AND result NOT NULL
GROUP BY code
HAVING Count(Student) > 1;
user_26415760

result <> NULL will not work, why?

When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE.

check this link for more details.

user_47620750
Related