Clever Geek Handbook

Selecting nullable rows in integer columns using mysql query

I have a table named "datatablecoulmn" with the following columns. enter image description here

now i want all rows where the FkID column 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'

They all return empty strings. Any help?

+3


source to share


5 answers


In MySQL, NULL is considered a "missing, unknown value", not a value. Any arithmetic comparison with NULL does not return true or false, but returns NULL instead. So NULL! = 'C' returns NULL, not true.

Use the condition IS NULL

in your request and try this

SELECT * FROM `datatablecoulmn` WHERE `FkID` IS NULL

MySQL provides three operators for handling NULL values



IS NULL: The operator returns true if the column value is NULL.

NOT NULL: The operator returns true if the column value is not NULL.

<=>: The operator compares values ​​that (unlike the = operator) are true even for two NULL values.

You can refer to these links for more

Link 1 , Link 2 , Link 3

+7


source


NULL

is a value, similar infinity

is a number. In other words, not at all. NULL

- lack of certain information.

For the same reason that NaN

(not a number) in IEEE754 floating point is not equal to other instances (or even the same instance) NaN

, nothing in SQL is equal NULL

, including NULL

.

Something that might sound strange, but when you think about the goal NULL

, about specifying unknown or invalid values, it makes sense.



To find out if there is a value NULL

, you should use something like:

where COLUMN_NAME is null

More details about working with NULL

in MySQL can be found here .

+2


source


You cannot compare with NULL

. So you need to check YourColumn IS NULL

(or maybe YourColumn IS NOT NULL

.

+2


source


Use something like:

SELECT * FROM `datatablecoulmn` WHERE `FkID` is NULL

NULL is a placeholder to say there is no value. This is why you can use IS NULL / IS NOT NULL as predicates for such situations, rather than = or! = Or <> which are used by values.

+1


source


Here's another way to exclude records using FkID

NOT NULL:

SELECT D1.*
FROM datatablecoulmn D1
WHERE NOT EXISTS (SELECT D2.* 
                  FROM datatablecoulmn D2
                  WHERE D2.`FkID` IS NOT NULL)
0


source






More articles:


All Articles