Why does column = NULL return no rows?

Question

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 ??

Answer 1

From Null (SQL)

Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as ''). Null is defined by the ISO SQL standard as different from both an empty string and the numerical value 0, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.

Also from SET ANSI_NULLS

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

Answer 2

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 = ''

Answer 3

NULL means no data (not even blank or empty stuff), so Column = NULL is not going to work although Column IS NULL should return rows with that column as NULL. But you're inserting '' value so comparing with NULL won't return any rows.

Answer 4

'' - it`s mean empty string. If you want to insert NULL,you need yo use

 INSERT INTO MyTab 
          VALUES (22, '', NULL, 4)

Answer 5

As others said, first query returns no output because you cannot check for NULL with equals operator. The second query is more puzzling - my only guess is that you are using Oracle which treats empty strings as NULLs.

The answer to the question about values in Sex and DOB fields would depend on the type of those fields and database engine you are working with.