sql SELECT return values matching NULL OR Another Value

Question

This question seems really basic, but I cant find the answer by google, and I've spent the last hour testing with no luck. Im having difficulties trying to return a set of rows. I want the rows that equal 11, 1, or Null. I think it is Null that is messing me up. Below is just one of the many tests I ran...and I know it doesn't work. Any help is REALLY appreciated. Thanks in advance

SELECT * FROM have
    WHERE have.flag = 11 || 1 || NULL;

Adam Wenger · Accepted Answer · 2012-03-15 04:01:10Z

4

You will need to treat NULL separately from 1 and 11, then just put the values into your where clause.

SELECT *
FROM have AS h
WHERE h.flag IS NULL
   OR h.flag IN (1, 11);

edited Mar 15, 2012 at 4:01

answered Mar 15, 2012 at 3:28

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

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

3 Comments

mu is too short Over a year ago

Actually, || does mean logical-OR in MySQL: dev.mysql.com/doc/refman/5.5/en/…

Adam Wenger Over a year ago

@muistooshort Thanks for the reference on ||, hadn't seen that before.

mu is too short Over a year ago

MySQL makes you use the CONCAT function for string concatenation, sigh.

vol7ron · Accepted Answer · 2012-03-15 03:38:09Z

1

Just as an alternative:

SELECT *
FROM   have
WHERE  nullif(nullif(flag,1),11) IS NULL

answered Mar 15, 2012 at 3:38

vol7ron

42.3k22 gold badges126 silver badges178 bronze badges

2 Comments

Ami Over a year ago

Doesn't the function against the column negate the ability to use any index for the WHERE criteria?

vol7ron Over a year ago

That's generally true, assuming there is an index. Not sure how MySQL handles indexes, but Postgres allows you to index functions. Still, I'd go with Adam's answer for the clarity and that reason, even if the question didn't specify. -- I only meant for this answer to be an educational alternative.

Chetter Hummin · Accepted Answer · 2012-03-15 03:44:16Z

1

Alternatively, you could go for

select * from have as h where
IFNULL(h.flag,1) in (1,11)

edited Mar 15, 2012 at 3:44

answered Mar 15, 2012 at 3:38

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

4 Comments

vol7ron Over a year ago

You should change the first 11 to a 1, for less characters :)

Chetter Hummin Over a year ago

@vol7ron I didn't see that. Thanks!

vol7ron Over a year ago

It's not going to make or break anything. Also the have_flag can be changed to just flag

Ami Over a year ago

Doesn't the function against the column negate the ability to use any index for the WHERE criteria?

cctan · Accepted Answer · 2012-03-15 04:10:24Z

1

This is the explanation:

SELECT * FROM have
    WHERE have.flag = 11 || 1 || NULL;

What you are stating is actually:

(have.flag = 11) || 1       || NULL
true || 1                   || NULL
1                           || NULL
1


(have.flag = 11) || 1       || NULL
false || 1                  || NULL
1                           || NULL
1

It will always evaluate to 1.
= has higher precedence than ||.
@Adam Wenger's answer is correct.

answered Mar 15, 2012 at 4:10

cctan

2,0233 gold badges21 silver badges31 bronze badges

1 Comment

Ami Over a year ago

True. Even in the simplest of cases, the query should be WHERE have.flag = 1 || have.flag = 11 || have.flag IS NULL. The column should be in each condition in the WHERE criteria.

Collectives™ on Stack Overflow

sql SELECT return values matching NULL OR Another Value

4 Answers 4

3 Comments

2 Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related