CODEWITHSUNDEEP
mysqlsqlnull
Xeoncross
Xeoncross

Reputation: 57184

How to handle null in SQL conditional clauses?

I recently found that a query would not work on some of my records if the values where null. Apparently in SQL, null cannot be compared to anything - even null.

UPDATE company SET views = IF(views, views + 1, 1),
trendingViews = IF(viewed != '2012-05-21 00:00:00', 1, trendingViews + 1),
viewed = IF(viewed != '2012-05-21 00:00:00', '2012-05-21 00:00:00', viewed)
WHERE id = '3024'

How can I change this query to conditionally check for the values provided and null?

Upvotes: 1

Views: 4796

Answers (6)

MachinusX
MachinusX

Reputation: 541

Caveat: am not a mysql person, but am a TSQL person. First, a comment on the general logic of one of your conditional update statements: when you first check 'viewed' for a specific value, and if 'viewed' is NOT of that value, SET it to that value, you are working harder than necessary.

What I mean is if you know that you want all records w/an ID of 3024 to have the same timestamp for the 'viewed' column, then, checking first FOR whatever value is in the column and only updating those that do not match, is not needed and so is additional work. If your intention is that all 'viewed' (where Id = '3024') should be set to '2012-05-21 00:00:00', then, just set them to that value. No need to consider what their original value was..you don't care.

So instead of:

viewed = IF(viewed != '2012-05-21 00:00:00', '2012-05-21 00:00:00', viewed)

you would just have:

viewed = '2012-05-21 00:00:00'

Second, you can use 'or' and/or 'coalesce' in your if statement so that null values will not be ignored. By using OR with IS NULL and/or coalesce with a non-null default value, you can force any null values to be updated regardless. (In your particular example, using coalesce with a default value of 0, will have the effect of setting prior null values in your 'trendingViews' column to 1, because you are incrementing by 1, and you would be using 0 as your default value for null instances.)

Example: 

trendingViews = IF(viewed IS NULL = 1 OR viewed != '2012-05-21 00:00:00', 1, COALESCE(trendingViews, 0) + 1),

The first line of your update statement...the part that sets 'views' confuses me, because I don't know if your intent is to check the existence of the column/field named 'views', or if you were trying to check whether the value in that 'views' column was non-null. If your aim was to check whether it was not null, and then increment it by 1 in that case, and if it was null, then set it to 1, then this could be done:

    views = IF(views IS NULL != 1, views + 1, 1),

So here is the whole statement that I would test (in MYSQL):

UPDATE company SET 
    views = IF(views IS NULL = 0, views + 1, 1),
    trendingViews = IF(viewed IS NULL = 1 OR viewed != '2012-05-21 00:00:00', 1, COALESCE(trendingViews, 0) + 1), 
    viewed = '2012-05-21 00:00:00'
WHERE id = '3024';

Please remember, I do NOT use MYSQL .... have NOT tested these MYSQL changes. If I was doing this in TSQL the following statement would work:

    UPDATE company SET 
        [views] = (CASE WHEN [views] IS NOT NULL THEN [views] + 1 ELSE 1 END),
        trendingViews = CASE WHEN viewed IS NULL OR viewed != '2012-05-21 00:00:00' THEN 1 ELSE COALESCE(trendingViews, 0) + 1 END, 
        viewed = '2012-05-21 00:00:00'
    WHERE id = '3024';
    go

Upvotes: 1

eggyal
eggyal

Reputation: 125855

In addition to the other answers given, you can also use the NULL-safe comparison operator <=>.

Upvotes: 2

Alex
Alex

Reputation: 14618

Use either Coalesce(value1, ... valueN) or isnull(value1, value2) to define an alternative non-null value for null fields.

Coalesce will return the first NON-NULL value from the list of arguments. Isnull will return the second argument value in case the first one is null

Upvotes: 4

PinnyM
PinnyM

Reputation: 35533

use COALESCE:

UPDATE company 
SET views = COALESCE(views, 0) + 1,
    trendingViews = IF(viewed != '2012-05-21 00:00:00', 1, trendingViews + 1),
    viewed = IF(viewed != '2012-05-21 00:00:00', '2012-05-21 00:00:00', viewed)
WHERE id = '3024'

Upvotes: 2

krock
krock

Reputation: 29619

If you want to exclude results where a column is not null then you need to use is not null in your where clause:

select ...
where ...
and column is not null

Upvotes: 1

mellamokb
mellamokb

Reputation: 56769

The proper way to check for null is MyField IS NULL. Comparing directly to null, as you correctly state, does not work.

Upvotes: 1

Related Questions