Possible Duplicate:
Why does NULL = NULL evaluate to false in SQL server
If you generate a query to insert the data in table "MyTab" for column --- Age, Sex, DOB, ID
INSERT INTO MyTab
VALUES (22, '', '', 4)
What'll be the value in column Sex & DOB ? Is it NULL ?
If value is NULL then ---
SELECT * FROM MyTab
WHERE Sex=NULL
above query gives output ---- no rows selected --- why ??
if value is not NULL then ---
SELECT * FROM Mytab
WHERE Sex IS NULL
above query gives the output ---- how ??
You can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL .
What is a NULL Value? A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
A NOT NULL constraint is a rule that prevents null values from being entered into one or more columns within a table. A unique constraint (also referred to as a unique key constraint) is a rule that forbids duplicate values in one or more columns within a table.
Discussion: If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns. You need to list the columns after the DISTINCT keyword.
NULL
is a special value in SQL denoting the absence of data. As such, you cannot do queries like:
SELECT fields FROM table WHERE column = NULL
NULL
cannot be compared to anything, including NULL
itself. Instead, you'd need:
SELECT fields FROM table WHERE column IS NULL
However in your case you are really inserting an empty value in Sex
and DOB
.
And empty value is not NULL
. You'd have to query for:
SELECT fields FROM table WHERE column = ''
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With