Null? Whats that mean?

[jwk811]

Whats does the null thing do in a database.. everything ive done so far it just needed to be "not null" i was just wondering what the difference was  ;D thanks

[wildteen88]

null means empty/no value/nothing

[Perad]

Null means no value. Its used for optional database values. For example your profile contains many optional values which are null till you put something in them. Things like password and e-mail are NOT NULL

[jwk811]

ok but i still dont understand why or when that would be used... optional values?

[croix]

you would allow null values for fields that the user does not HAVE to fill in...

I.E. when filling out a form, there are often "required" fields, which would not allow a null value, and fields which are optional for the user to fill out, which will allow null values

[Psycho]

But, be careful here. With repsect to programming, different languages treat NULL differently.

NULL does not equal no value. In a true sense NULL means that there never was a value.

Here's an example. Suppose you had a form with two fields where a person could enter their first name and last name. Now, let's suppose the user enters a firstname, but not a lastname. If you used this code to enter the data:

[code]<?php
$sql = "INSERT INTO 'table' (fname, lname) VALUES(".$_POST[fname].", ".$_POST[lname].")";
?>[/code]

Then the value for lastname would NOT be null. It would have a value of an empty string. However if you used this code:

[code]<?php
$fields .= "fname";
$values .= $_POST[fname];
if ($_POST[lname] != "") {
  $fields .= ", lname";
  $values .= ", " . $_POST[lname];
}

if ($_POST[lname]) {}
$sql = "INSERT INTO 'table' (".$fields.") VALUES(".$values.")";
?>[/code]

Then there would be NO value inserted into the last name field. It doesn't have a value because it never existed. It's a very subtle, but important difference.

[btherl]

A critical difference between php's null and sql's null is that sql's null is not equal to itself.

$a = null; $b = null; if ($a === $b) print "null === null\n";

This will show that php null is equal to php null.  But

SELECT null = null;

in SQL will give you the result "false".  SQL's null is never equal to itself, because it represents "unknown value".

That's why you have to use "IS null" or "IS NOT null" to test for nullness in SQL, instead of using "= null".

However, people very often use null in sql to mean "no value" as well as "unknown value".  So whichever meaning you are using it for, you need to use the appropriate conditions.  Typically you'll use "IS null" when null means "no value", but you'll check for equality when it means "unknown value".