Hacker News Reader

Comment by stoolpigeon | Hacker News Reader

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.

View on HN

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?

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

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.

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!

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.

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).

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?

It's an option which can be enabled or disabled to taste. It's no more egregious than empty strings being null in Oracle, though I've no doubt there are more persuasive arguments for why Oracle is a better RDBMS.

Ah, thanks. I hadn't encountered that.

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

Precisely my point. Nothing equals null, but SQL server allows queries that use '= NULL' in them.

But that doesn't prevent = NULL from parsing into an AST containing the "IS NULL" opcode. It's syntax sugar.

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).

Reply on news.ycombinator.com