Bob, My guess is that you have a text value of '-0-', rather than a null in one row. SET NULL '-x-' temporarily and try your IS NULL syntax again.
"IS NULL" is SQL standard syntax. "NOT EXISTS" is very old grandfathered syntax, from DOS 2.x versions of R:BASE. It adds confusion, because there is an EXISTS operator in SQL standard SELECT syntax, with a very different meaning. We also found many examples in training in the old days where "EXISTS" and "NOT EXISTS" were linguistically confusing to end users. If, for example, you have a survey results table, with an integer column named PostGradDegree containing either a 1 for "true" or a zero for "false", a user could expect sensible results from the "sensible" syntax "WHERE PostGradDegree EXISTS" but would not get the expected result set. A syntax like "WHERE PostGradDegree IS NOT NULL" at least makes it clear you have to consider the values in the column, not just the NAME of the column. Bill [EMAIL PROTECTED] wrote: > This is not a major issue, just wanting to understand code better. > Why do I get a different result on the below select commands? > > R>sel ordnumber orderstatus from upsorders where orderstatus not exists > ordnumber ordersta > ---------------------- -------- > OR333910 -0- > OR333911 -0- > R>sel ordnumber orderstatus from upsorders where orderstatus is null > ordnumber ordersta > ---------------------- -------- > OR333911 -0- > Also note that the "not exists" statement takes much longer to > return the selection than the "is null" statement which is almost instant. > > I had a snippet of code that was not finding all the records. Simply > changing it to not exists fixed the problem, but I was wondering what > the difference is. > > Thanks, > -Bob
