CODEWITHSUNDEEP
sqlnullcomparisonoperators
anbisme
anbisme

Reputation: 163

SQL: comparisons and null values

Here's the code I'm working with:

SELECT 'HOPE', RPRAWRD_AIDY_CODE, COUNT(DISTINCT RPRAWRD_PIDM) AS NumberOfStudents,  Sum(RPRAWRD_PAID_AMT) AS AmountPaid
FROM FAISMGR.RPRAWRD, FAISMGR.RFRBASE
WHERE RPRAWRD_AIDY_CODE = '1213'
     AND RPRAWRD_FUND_CODE = RFRBASE_FUND_CODE
     AND RPRAWRD_AWST_CODE = 'ACPT'
     AND RPRAWRD_PAID_AMT > 0 
     AND RFRBASE_FSRC_CODE = 'HOPE'
GROUP BY RPRAWRD_AIDY_CODE;

The results yield 3952 records.

Some of the records contain NULL in RPRAWRD_PAID_AMT, and if I add

AND RPRAWRD_PAID_AMT IS NOT NULL

to the WHERE clause, I get exactly the same results: 3952 records. So, my question is: does >0 exclude null values because they evaluate as false, making IS NOT NULL unnecessary?

Upvotes: 0

Views: 99

Answers (2)

Colonel Thirty Two
Colonel Thirty Two

Reputation: 26569

Most operations on NULL in SQL return NULL, which evaluates as false in conditionals.

mysql> SELECT NULL > 0;
+----------+
| NULL > 0 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

So yes, RPRAWRD_PAID_AMT > 0 will filter out nulls, but it doesn't hurt to be explicit.

Upvotes: 0

juergen d
juergen d

Reputation: 204766

RPRAWRD_PAID_AMT > 0

will only return true if the value of RPRAWRD_PAID_AMT is greather than 0. Comparing to null results in unknown and this is also false.

So yes, you don't need an extra null check.

Upvotes: 1

Related Questions