int default NULL and o?

[mikerh9]

What is the difference for "int" type if default is NULL or o?
Thanks

[_RL_Ian]

Nothing.

[thorpe]

yes there is. NULL is NULL. meening the field contains no data. 0 is an integer to the value of zero.

[_RL_Ian]

Oh yea technically you're right. Sorry. I was thinking in code terms. Like empty() will return the same result if its NULL or 0.

[gkelley091565]

NULL is the absence of value. 0 is the value that sits between -1 and 1 on a number line.

Huge difference.

[jazz_snob]

Actually the original question asked the difference between NULL and o, which is clearly the letter 'o', not a zero ( which looks like this: 0 ).

[Sxooter]

It's also important to understand that there are consequences of each 0 and NULL.

For instance, stricly speaking, the following query should always return 0 rows:

select * from anytable where somefield=NULL;

Why? Because no two NULLS are considered equal. So, the proper syntax to retrieve all rows with nulls in them is:

select * from anytable where somefield IS NULL;

Notice the symantic difference here. IS NOT NULL is the opposite.

Also, NULLS always sort at the top or bottom of a set, and that position may or may not change based on a DESC in the order by, and this is completely legal per the SQL spec, I believe. It's implementation dependent.

Now, here's a thought exercise. What's the exact national debt? Don't know? Then that's a NULL. I can assure you, the national debt is NOT ZERO!

So, programmatically, they are different. Note that most, if not all, database layers in php have a xxx_field_is_null function available to them, so there IS A DIFFERENCE there too, if you care to pay attention to it.

Sadly, both MySQL's and Oracle's rather retarded handling of NULLs has led a whole generation of both experienced, knowledgeable DBAs and newbies all believing that NULL = "" = 0 = DEFAULT etc...

It's not any of those things, and it's kind of funny that MySQL has been busy fixing all their idiosyncracies about NULL, while Oracle happily rolls along thinking NULL = ""

[Lars_Berg]

To be strictly standard compliant the query

select * from anytable where somefield=NULL

should return an error.

[Sxooter]

Not according to the SQL 92 spec, which says:

8.2 <comparison predicate>

Function

     Specify a comparison of two row values.

     Format

     <comparison predicate> ::=
          <row value constructor> <comp op> <row value constructor>

     <comp op> ::=
            <equals operator>

(edited for brevity)
General Rules

     1) Let X and Y be any two corresponding <row value constructor
        element>s. Let XV and YV be the values represented by X and Y,
        respectively.

        Case:

        a) If XV or YV is the null value, then "X <comp op> Y" is un-
          known.

[Lars_Berg]

SQL 92 is obsolete. It was changed in SQL 99.

[Sxooter]

True, but the SQL 2003 spec says the exact same thing.

1) Let XV and YV be two values represented by <value expression>s X and Y, respectively. The result of:
X <comp op> Y
is determined as follows:
Case:
a) If either XV or YV is the null value, then
X <comp op> Y
is Unknown.

[Lars_Berg]

NULL is not a <value expression>

[Sxooter]

So, you're saying that a <value expression> can be a NULL, but NULL cannot be a <value expression>???

That seems counterintuitive, and given the reading of the SQL 2003 spec I've made so far, I see nothing to uphold your assertion.

Do you have a quote from the specification, or is this a baseless assertion?

[Lars_Berg]

I'm saying that the keyword NULL is not among the alternatives for

<value expression> nor <nonparenthesized value expression primary>

NULL can be used in a <row value constructor> (7.1)

i.e.

where (c1,c2) = (1,null)

is valid syntax

whereas

where c1 = null

is not.

[Sxooter]

Again, I ask for a citation on this. I have a citation that says a <value expression> can be a NULL, and can find none one way or another on whether or not NULL can be used as a <value expression> but see no reason why it can't. In fact, the language from section 7.1 seems pretty similar to the language in 8.2 that says a <value expression> can be NULL.

[Lars_Berg]

SQL 2003 6.25

<value expression>::=
<common value expression>
|<boolean value expression>
|<row value expression>

<common value expression>::=
<numeric value expression>
|<string value expression>
|<datetime value expression>
|<interval value expression>
|<user-defined type value expression>
|<reference value expression>
|<collection value expression>

sql 2003 6.3 <value expression primary>

<value expression primary>::=
<parenthesized value expression>
|<nonparenthesized value expression primary>
<parenthesized value expression>::=<left paren><value expression><right paren>
<nonparenthesized value expression primary>::=
<unsigned value specification>
|<column reference>
|<set function specification>
|<window function>
|<scalar subquery>
|<case expression>
|<cast specification>
|<field reference>
|<subtype treatment>
|<method invocation>
|<static method invocation>
|<new specification>
|<attribute or method reference>
|<reference resolution>
|<collection value constructor>
|<array element reference>
|<multiset element reference>
|<routine invocation>
|<next value expression>

In those cases where the keyword NULL is allowed (apart from row constructors) it is specially indicated, e.g. as the result operand for a case expression

sql 2003 6.11 case expression

<result>::=
<result expression>
|NULL

<result expression>::=<value expression>

I have a citation that says a <value expression> can be a NULL

That was valid in SQL 92 and subsequently changed in SQL99

[Sxooter]

Lars Berg wrote:

That was valid in SQL 92 and subsequently changed in SQL99

The quotation I refer to came from the 2003 standard. However, it only says a <value expression> can be the null value, it doesn't say you can use the keyword NULL for it. My point being that I found no actual quote in the documentation that said what you said in your post:

In those cases where the keyword NULL is allowed (apart from row constructors) it is specially indicated, e.g. as the result operand for a case expression

And at first thought that was part of a citation, but now I'm not sure either way? Is it part of a citation, or your own interpretation?

However, it seems that allowing someone to use an expression that evaluates to null but not the keyword NULL in the same place makes little sense, and could even make testing certain behaviours more difficult to do. Of course, I don't want to be the first person to accuse the SQL spec creators of bowing to common sense or anything, so it's quite possible they did outlaw this usage.

By your logic, the uasge of "select 1 + NULL" would generate an exception since the definition of <numeric value expression> doesn't include NULL, but I can't imagine that being illegal. Basically, what you're saying makes MANY uses of the NULL keyword illegal. The implications would be huge.

[Lars_Berg]

By your logic, the uasge of "select 1 + NULL" would generate an exception since the definition of <numeric value expression> doesn't include NULL, but I can't imagine that being illegal.

It's not my logic, it's the syntactical rules for SQL that disallows that construction.

In those cases where the keyword NULL is allowed (apart from row constructors) it is specially indicated, e.g. as the result operand for a case expression

Those are my words and not a quote from the standard document.

However, it seems that allowing someone to use an expression that evaluates to null but not the keyword NULL in the same place makes little sense, and could even make testing certain behaviours more difficult to do.

It makes sense to me. I fail to see how this affects testing,

select 1 + cast(null as int) from t

is a valid statement.