2 Queries That Should Return The Same Data But Don't

I have 2 Informix queries that I believe should return the same data but do not. The first query uses a subquery as a filter and improperly returns no rows. The second is performed

Solution 1:

Any time I read "Not In ... subquery ... returns no rows" I'm pretty sure I know the answer!

I presume select commit_no from bolx returns some NULL values?

The presence of a NULL in a NOT IN guarantees that no results will be returned.

foo NOT IN (bar, NULL) is equivalent to

foo <> bar and foo <> NULL

The foo <> NULL part will always evaluate to unknown and the AND can never evaluate to true unless all conditions evaluate to true.