SQL where IS NULL returns rows where data is NOT NULL

Question

I am trying to get all the rows where start date is above current date or end date is above current date and whitepaper column is null.

For some reason this sql returns what i expect EXCEPT it also returns the rows where whitepaper is NOT NULL, if i turn the sql around and say whitepaper IS NOT NULL it will return everything as expected all the ones where whitepaper is not null and not the ones where whitepaper is null?

The 'And' should it not specify that this has to be fullfilled?

SQL

SELECT id, 
       name, 
       whitepaper, 
       date_start, 
       date_end 
FROM approved 
WHERE  date_start >= CURDATE() OR date_end >= CURDATE() 
       AND whitepaper IS NULL 
ORDER BY date_end DESC 
LIMIT 10

Luc M · Accepted Answer · 2018-04-20 16:09:56Z

2

As other told you, you have to use parenthesis around the OR condition.

Why ? Because the OR has a lower priority than AND

Without parenthesis, your query is in fact

 SELECT id, 
        name, 
        whitepaper, 
        date_start, 
        date_end  FROM approved  
 WHERE   date_start >= CURDATE() 
         OR
         ( 
           date_end >= CURDATE() 
           AND
           whitepaper IS NULL
         )
         ORDER BY date_end DESC  LIMIT 10

answered Apr 20, 2018 at 16:09

Luc M

17.5k27 gold badges78 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

flyingfox · Accepted Answer · 2018-04-20 15:59:36Z

1

your OR should in (),try with below:

 SELECT id, name, whitepaper, date_start, date_end 
    FROM approved 
    WHERE  (date_start >= CURDATE() OR date_end >= CURDATE())
     AND whitepaper IS NULL 
     ORDER BY date_end DESC LIMIT 10

answered Apr 20, 2018 at 15:59

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

Comments

nacho · Accepted Answer · 2018-04-20 16:01:42Z

1

Try putting your ORs in parentheses:

SELECT id, name, whitepaper, date_start, date_end 
FROM approved 
WHERE  (date_start >= CURDATE() OR date_end >= CURDATE()) AND whitepaper IS NULL 
ORDER BY date_end DESC LIMIT 10

edited Apr 20, 2018 at 16:01

nacho

5,3772 gold badges28 silver badges35 bronze badges

answered Apr 20, 2018 at 16:00

user6921149

Comments

A Singh · Accepted Answer · 2018-04-21 06:15:50Z

0

Well isnull you may use along with expression also you may check empty data in whitepaper field here is my suggestion SELECT id, name, whitepaper, date_start, date_end FROM approved WHERE (date_start >= CURDATE() OR date_end >= CURDATE()) AND (ISNULL(whitepaper) or whitepaper='') ORDER BY date_end DESC LIMIT 10

edited Apr 21, 2018 at 6:15

answered Apr 20, 2018 at 16:09

A Singh

94 bronze badges

1 Comment

Eric Brandt Over a year ago

Thanks for jumping in, but there are issues with your post. You didn't explain what you did to help the OP see his error, or what distinguishes your answer from the ones that came before you. Also, you're code isn't formatted as code. I'm not downvoting your answer, but others may unless you fix it up a little. stackoverflow.com/help/how-to-answer

Collectives™ on Stack Overflow

SQL where IS NULL returns rows where data is NOT NULL

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related