Menu
Let's look at an example of how to use MySQL IS NULL in a SELECT statement: SELECT * FROM contacts WHERE last_name IS NULL; This MySQL IS NULL example will return all records from the contacts table where the last_name contains a NULL value.
SELECT * FROM yourTableName WHERE yourSpecificColumnName IS NULL OR yourSpecificColumnName = ' '; The IS NULL constraint can be used whenever the column is empty and the symbol ( ' ') is used when there is empty value.
“IS NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL. “NOT NULL” is the keyword that performs the Boolean comparison. It returns true if the supplied value is not NULL and false if the supplied value is null.
userPosts.value can contain one of two values: 0 or 1.
I am Left Joining userPosts to my Posts table.
I want to get all posts from my Posts table where userPosts.value = 0 as well as all posts that do not have any userPosts.value at all (thus, NULL).
The following only get me posts where value = 0 but no NULL:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value != 1
ORDER BY $wpdb->posts.post_date DESC
The following only gets me posts where value = NULL:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
but this yields no results at all:
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
AND userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
and this does get me posts with value = 0 as well as NULL but it repeats all my NULL posts three times!
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
OR userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
So what am I doing wrong?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With