Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mr. Muskrat (4572)

Mr. Muskrat
  reversethis-{moc ... ta} {tarksum.rm}
I'm married with 2 girls. I work as a full time Perl programmer for a Land Mobile Radio company in the Dallas/Fort Worth area.

I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.

My other blog [blogspot.com]

Journal of Mr. Muskrat (4572)

Wednesday August 29, 2007
03:04 PM

Some Days I Feel Like I Know Nothing About Oracle

[ #34260 ]

Today is another one of those days.

I'm doing a query against a table using a primary key and a check if two of the other columns are not the same.

SELECT column_list FROM table_name WHERE pk_column = some_var AND column_x != column_y;

One of the columns can be null. Apparently I have never tried to do this with Oracle until now.

Initially I expected that NULL is not equal to some non-NULL value in the same way that an undef is not equal some defined value. However, I know why Oracle does this (it's in the ANSI standard but no one else that I know of does it this way).


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Some Days I Feel Like I Know Nothing About Oracle 15 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.

  • ... I didn't design it.

    The new query looks like:
    SELECT column_list FROM table_name WHERE pk_column = some_var AND ( column_y IS NULL OR column_x != column_y);

  • Surely that behaviour is the same in PostgreSQL too? Any attempt to compare a NULL value to any other value (including another NULL) will result in NULL rather than true or false.
  • I seem to recall hitting this once, and from what I could tell it reflects a sort of purist view by Oracle.

    You have something, and you have nothing. And you can't compare something with nothing. Thus, they can't be "different" because there's nothing there to be different with.

    Takes a bit of mind reversal...
  • If you know that the possibly null column can not be some value (say a single space) or you want to assume null is the same as some value, then you can use:

    where col1 != NVL(col2, ' ')
    • Oh, and it's not just Oracle (and PostgreSQL) that treats NULL's this way.

    • And if you want to be SQL-92 compliant, you can use COALESCE, which works the same as NVL (but sounds better, even though it takes longer to type).

    • Is that right?

      I am thinking right now that the correct way to write this test would be WHERE NVL(col1 != col2, FALSE). So it would return the resulting boolean value from all comparisons of existent values, but for comparisons involving NULL it would default the resulting NULL to FALSE. Basically you’re saying “compare the columns for inequality, and if they can’t be compared, then treat them as unequal.”

      By doing it this way you avoid semi-predicate problems.

      • I see what you're saying...except that the NVL() should return TRUE if the '!=' result is null (at least that's what the OP seems to want). And I know he said that only one of the columns could be null, but I'm so used to potentially both columns being null, and wanting to treat that case as the columns being equal, that I'm used to wrapping the columns in NVL() individually...and not thinking about brilliant shortcuts :-)
  • MySQL does the same. I bet SQLite does too.
    • Is that a 5.0 thing or has it always done it this way? I may be misremembering but I thought that MySQL 4 treated NULLs as either empty string or 0 depending on the equality tests.

  • ... but I really thought that I had seen a database treat NULLs like Perl treats undefs. I guess it's always possible that my memory is playing tricks on me. It certainly wouldn't be the first time. I did a lot of stupid stuff in my youth that almost certainly destroyed vast amounts of brain cells.

    The absence of a value shouldn't prevent you from checking [in]equality. But maybe I'm the only one that thinks that way. :)

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.