MYSQL include NULL in query

Question

$sql = "SELECT * FROM orders WHERE order_number>=$lower AND order_number<=$upper";

I migrated servers recently and on the previous server this statement included ALL records between $upper and $lower.

The new server excludes the NULL records between $upper and $lower.

Incomplete orders are saved consecutively without order_number(s); and a NULL value.

I assume there is a setting in the MYSQL.conf file. Or I am using a different version of MYSQL that no longer supports automatically including the NULL value in a query.

Can't say why the behavior changed (and I'm surprised any version of MySQL could be made to return NULLs with that query), but the new behavior you're seeing is correct and the old behavior is wrong. — Larry Lustig
– Larry Lustig, Commented Sep 7, 2011 at 18:07
MySQL client version: 5.0.77 | Changing the NULL value may work. But the system is running and I cant/wont make changes to test this. — Jeff
– Jeff, Commented Sep 8, 2011 at 15:18

Thomas · Accepted Answer · 2011-09-07 18:05:01Z

9

I have no idea why the old server included null values as that would violate a fundamental rule about how comparison to nulls should work. If you want nulls your query should be something like:

Select ...
From orders
Where ( order_number >= $lower And order_number <= $upper )
    Or order_number Is Null

answered Sep 7, 2011 at 18:05

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

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

4 Comments

Jeff Over a year ago

Thanks. "Or order_number Is Null" returns all NULL values in the database. I want to limit it to the Null records between the values.

Thomas Over a year ago

@Jeff - What do you mean by limiting the results to the Null rows "between values"? A null order_number isn't "between" anything.

Thomas Over a year ago

@Jeff - Said another way, if order_number is null, how do would you determine whether a given row with a null order_number should or should not appear in the resultset? Remember that tables are collections of unordered sets of rows. Thus, there is no concept of sequence of entry unless you have columns which specifically capture this information. If that were the case, then we'd need to know how to determine which rows should appear should the order_number be null.

Jeff Over a year ago

I was assuming that there is a setting that determines weather ALL the rows, ordered by their insertions into the database, will be returned when selecting ALL from the table. I was assuming this as this is how the database was functioning on the previous server. I gather that this is not the case and I will need to use the record number related to the order number to select all as a workaround.

Collectives™ on Stack Overflow

MYSQL include NULL in query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related