CODEWITHSUNDEEP
mysqlsqlwhere-clause
Shaharyar
Shaharyar

Reputation: 12439

Logical condition in WHERE - Incorrect results

Dataset: (Just highlighting error condition data, other conditions work fine)

enter image description here

Query:

SELECT  `id`,   `uid`,  `activity_type`,    `source_id`,    `parent_id`,    `parent_type`,  `post_id`,  `status`
FROM    `user_activity` AS `user_activity`
WHERE
    `user_activity`.`activity_type` IN(
        'STATUS_UPDATE',
        'PROFILE_PICTURE_UPDATED',
        'PROFILE_COVER_UPDATED',
        'ALBUM'
    )
AND `user_activity`.`uid` = '13'
AND `user_activity`.`status` = 'ACTIVE'
-- AND `user_activity`.`parent_type` <> 'ALBUM' -- query works wihtout this condition
ORDER BY    id DESC LIMIT 10

The query works fine and gets the desired results, but when I add the condition:

AND `user_activity`.`parent_type` <> 'ALBUM'

query returns no results.

I don't understand what's the problem in this query.

Here is the fiddle.

Upvotes: 2

Views: 56

Answers (4)

Thomas G
Thomas G

Reputation: 10216

If I consider the data in the SQLfidle, There's no problem in your query. The only row that it is supposed to return, following YOUR logic, is a row containing a NULL value in parent_type

MySQL logic is just diffrent than yours: When you use the <> or != operator, the NULLs are omited and never returned. If you want to also get the NULLs, do:

AND (`user_activity`.`parent_type` <> 'ALBUM' OR `user_activity`.`parent_type` IS NULL)

or this

AND coalesce(`user_activity`.`parent_type`,'whateveryouwanthere') <> 'ALBUM'

Upvotes: 1

Vipin Jain
Vipin Jain

Reputation: 3756

Try This

SELECT  `id`,   `uid`,  `activity_type`,    `source_id`,    `parent_id`,    `parent_type`,  `post_id`,  `status`
FROM    `user_activity` AS `user_activity`
WHERE
    `user_activity`.`activity_type` IN(
        'STATUS_UPDATE',
        'PROFILE_PICTURE_UPDATED',
        'PROFILE_COVER_UPDATED',
        'ALBUM'
    )
AND `user_activity`.`uid` = '13'
AND `user_activity`.`status` = 'ACTIVE'
AND IFNULL(`user_activity`.`parent_type`,'') <> 'ALBUM'
ORDER BY    id DESC LIMIT 10

Upvotes: 1

N West
N West

Reputation: 6819

SQL uses three valued logic. The NULL <> 'ALBUM' and NULL = 'ALBUM' both evaluate to UNKNOWN. You need to use IS NOT NULL or IS NULL to compare with nulls.

Upvotes: 4

Lamak
Lamak

Reputation: 70638

The user_activity.parent_type <> 'ALBUM' condition also filters every NULL value on that column, since NULL <> 'ALBUM' isn't true, is undetermined. So you can use something like this:

AND (`user_activity`.`parent_type` <> 'ALBUM' OR `user_activity`.`parent_type` IS NULL)

Or:

AND COALESCE(`user_activity`.`parent_type`,'') <> 'ALBUM'

Upvotes: 3

Related Questions