I have a table named 'datatablecoulmn' with the following columns.
now i want all rows where the column FkID is NULL.FkID is an integer field
i tried the following queries
SELECT * FROM `datatablecoulmn` WHERE `FkID`=NULL
SELECT * FROM `datatablecoulmn` WHERE `FkID`<1
SELECT * FROM `datatablecoulmn` WHERE `FkID`='null'
All of these returns empty rows .Any help?
In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.
Use IS NULL condition in your query and try like this
SELECT * FROM `datatablecoulmn` WHERE `FkID` IS NULL
For handling NULL values MySQL provides three operators
IS NULL: operator returns true if column value is NULL.
IS NOT NULL: operator returns true if column value is not NULL.
<=>: operator compares values, which (unlike the = operator) is true even for two NULL values.
You can refer to these links for more
NULL is a value like infinity is a number. In other words, not at all. NULL is the absence of certain information.
Hence, for the same reason that NaN (not a number) in IEEE754 floating point is not equal to other instances of NaN (or even the same instance), nothing in SQL is equal to NULL, including NULL.
That's something that may sound strange but, when you think of the purpose of NULL, that of specifying unknown or inappropriate values, it makes sense. If you don't know what A is and you don't know what B is, you can't really say for certain that they're equal.
Therefore, with comparisons with NULL, MySQL will give you back NULL (unknown), as per the following transcript:
> select 1 = 1 as A, 1 = NULL as B, NULL = NULL as C, NULL is NULL as D;
A B C D
1 NULL NULL 1
In order to see if a value is NULL, you have to use is rather then =, as per the "D" case above. For your case, that would be something like:
SELECT * FROM `datatablecoulmn` WHERE `FkID` is NULL
More details on working with NULL in MySQL can be found here.
And, though this isn't actually relevant to your question, you may want to think about the spelling of your table name (assuming it's not just a typo made when posting the question). Specifically "column" rather than "coulmn".
You can't compare with NULL. So you gotta check for YourColumn IS NULL (or maybe YourColumn IS NOT NULL.
Use something like:
SELECT * FROM `datatablecoulmn` WHERE `FkID` is NULL
NULL is a placeholder to say there is the absence of a value. Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations and not = or != or <> which is used by values.
Here is another way to exclude the records with FkID is NOT NULL:
SELECT D1.*
FROM datatablecoulmn D1
WHERE NOT EXISTS (SELECT D2.*
FROM datatablecoulmn D2
WHERE D2.`FkID` IS NOT NULL)
