4 ms·

Oracle - in a number of situations is most certainly 'better'. I can think of 3 things that prove this to be so, " = NULL", RAC and running an OS other than MS

Oracle - in a number of situations is most certainly 'better'. I can think of 3 things that prove this to be so, " = NULL", RAC and running an OS other than MS Windows on your server.

sjwright 15y ago
NULL shouldn't equal NULL. If two customers have a phone number of NULL, that doesn't mean they have the same phone number.
- stoolpigeon 15y ago
  Precisely my point. Nothing equals null, but SQL server allows queries that use '= NULL' in them.
  - jrockway 15y ago
    But that doesn't prevent = NULL from parsing into an AST containing the "IS NULL" opcode. It's syntax sugar.
  - dspillett 15y ago
    Only if you explicitly turn the ANSI_NULLs option off, IIRC more conformant behaviour has been the default since at least version 7. Of course it does still have irritations like trailing spaces not being considered significant in string comparisons (to pluck a random example from the air) but so do other databases, especially those that have been around many years so have their on legacy to be backwardly compatible with lest the users revolt, including Oracle (empty strings being considered NULL for instance).
cafard 15y ago
Sorry, I don't quite get the first point. Do you mean that you prefer empty strings to be treated as NULL?
- stoolpigeon 15y ago
  MS SQL Server allows statements that include '= NULL' in them. Like, 'SELECT * from foo WHERE foo_id = NULL'. One cannot do this in Oracle. This, in my opinion, indicates that Oracle is a better RDBMS.
  - yread 15y ago
    I wouldn't argue for a "better" RDBMS with such a small silly feature. Obviously SQL Server is meant for developers (not DBAs) who are used to writing myCSVar = null. Besides you can turn it back on with SET ANSI_NULLS ON. Oracle also has a plenty of features which are not compatible with the standard (and which can't even be turned off) '' (empty string) being the same thing as null is really neat though!
    - stoolpigeon 15y ago
      That's just a pet peeve. The other two are bigger. But really it just irked me that he felt it necessary to add that Oracle wasn't better than MS SQL Server. There are cases where it most definitely is better. There may be cases where MS SQL Server is better (probably especially if one is running an all MS stack) but having worked as a full time DBA with both I know where I prefer to work. Of course I'm not a fan of windows either - so some of my preference for Oracle and PostgreSQL come from the fact that I get to manage them in my preferred environment.
  - matwood 15y ago
    MSSQL probably has the '= NULL' syntax around for backwards compatibility. It does support the 'is null' and 'is not null' syntax that is in Oracle (which is standard IIRC).
  - xxdiamondxx 15y ago
    I've always used 'is null' when working with SQL Server, but I guess the fact that it even allows you to use '= NULL' is what troubles you?
  - thom 15y ago
CWuestefeld 15y ago
SQL Server has a setting ANSI_NULLS, which defaults to on. In this state, the behavior is exactly as you expect. It takes explicit action to change it from the standard. That said, the SQL standard is already inconsistent in this respect, which annoys me. You are allowed to use equality in setting a value: update mytable set mycol=NULL where mykey=2 If I'm allowed to assign a value of null, why can't I compare it? If NULL is so special that its value cannot be compared, then what does it mean when I assign that "value"? The assignment operation makes a copy of the specified value into the target column; how can I make that copy of the specified value if it's impossible to say that two NULLs are the same?

Oracle - in a number of situations is most certainly 'better'. I can think of 3 things that prove this to be so, " = NULL", RAC and running an OS other than MS Windows on your server.

sjwright 15y ago

NULL shouldn't equal NULL. If two customers have a phone number of NULL, that doesn't mean they have the same phone number.

stoolpigeon 15y ago
Precisely my point. Nothing equals null, but SQL server allows queries that use '= NULL' in them.
- jrockway 15y ago
  But that doesn't prevent = NULL from parsing into an AST containing the "IS NULL" opcode. It's syntax sugar.
- dspillett 15y ago
  Only if you explicitly turn the ANSI_NULLs option off, IIRC more conformant behaviour has been the default since at least version 7. Of course it does still have irritations like trailing spaces not being considered significant in string comparisons (to pluck a random example from the air) but so do other databases, especially those that have been around many years so have their on legacy to be backwardly compatible with lest the users revolt, including Oracle (empty strings being considered NULL for instance).

cafard 15y ago

Sorry, I don't quite get the first point. Do you mean that you prefer empty strings to be treated as NULL?

stoolpigeon 15y ago
MS SQL Server allows statements that include '= NULL' in them. Like, 'SELECT * from foo WHERE foo_id = NULL'. One cannot do this in Oracle. This, in my opinion, indicates that Oracle is a better RDBMS.
- yread 15y ago
  I wouldn't argue for a "better" RDBMS with such a small silly feature. Obviously SQL Server is meant for developers (not DBAs) who are used to writing myCSVar = null. Besides you can turn it back on with SET ANSI_NULLS ON. Oracle also has a plenty of features which are not compatible with the standard (and which can't even be turned off) '' (empty string) being the same thing as null is really neat though!
  - stoolpigeon 15y ago
    That's just a pet peeve. The other two are bigger. But really it just irked me that he felt it necessary to add that Oracle wasn't better than MS SQL Server. There are cases where it most definitely is better. There may be cases where MS SQL Server is better (probably especially if one is running an all MS stack) but having worked as a full time DBA with both I know where I prefer to work. Of course I'm not a fan of windows either - so some of my preference for Oracle and PostgreSQL come from the fact that I get to manage them in my preferred environment.
- matwood 15y ago
  MSSQL probably has the '= NULL' syntax around for backwards compatibility. It does support the 'is null' and 'is not null' syntax that is in Oracle (which is standard IIRC).
- xxdiamondxx 15y ago
  I've always used 'is null' when working with SQL Server, but I guess the fact that it even allows you to use '= NULL' is what troubles you?
- thom 15y ago

CWuestefeld 15y ago

SQL Server has a setting ANSI_NULLS, which defaults to on. In this state, the behavior is exactly as you expect. It takes explicit action to change it from the standard. That said, the SQL standard is already inconsistent in this respect, which annoys me. You are allowed to use equality in setting a value: update mytable set mycol=NULL where mykey=2 If I'm allowed to assign a value of null, why can't I compare it? If NULL is so special that its value cannot be compared, then what does it mean when I assign that "value"? The assignment operation makes a copy of the specified value into the target column; how can I make that copy of the specified value if it's impossible to say that two NULLs are the same?