Difference between (NULL) and empty in mysql column values

Question

I am working in a company where we use Spring -Hibernate and mysql database at backend.

There is a table tc_wallet
In this table i have an column tips_type which has values

Collection,Payable
'' (empty)
NULL --> No value has been initialized

Now when i fire a query:

SELECT * 
FROM `tc_wallet` 
WHERE 
    login_id = 'gaurav.wakharkar' 
    AND `delete_flag` = 'F' 
    AND `tips_type` != 'Collection'

I get results which has column value as '' (empty).

Login_id            tips_type   
gaurav.wakharkar                 
gaurav.wakharkar         
gaurav.wakharkar

But even (NULL) is != 'Collection' should satisfy the above condition.
So according to me the result should have been .

Login_id            tips_type   
gaurav.wakharkar                 
gaurav.wakharkar         
gaurav.wakharkar       
gaurav.wakharkar    (NULL)   
gaurav.wakharkar    (NULL)

Is there some issue while checking/comparing values with (NULL) ?
Does it behave differently ?

maybe this will help to understand https://stackoverflow.com/questions/1106258/mysql-null-vs — shushu304, Mar 30 '19 at 09:38
Typing `mysql null empty string` into any search engine would have answered that for you. — MatBailie, Mar 30 '19 at 09:45
You might want to check this: [three valued logic](https://en.wikipedia.org/wiki/Three-valued_logic) — Michał Turczyn, Mar 30 '19 at 09:52

score 3 · Accepted Answer · answered Mar 30 '19 at 09:40

3

To check for nullness, you want to use IS NULL. Comparing NULL to something else with the equality operator (or the inequality operator) is always false.

Consider:

SELECT * 
FROM `tc_wallet` 
WHERE 
    login_id = 'gaurav.wakharkar' 
    AND `delete_flag` = 'F' 
    AND (`tips_type` IS NULL OR `tips_type` != 'Collection')

answered Mar 30 '19 at 09:40

GMB

216,147
25
84
135

1

You should mention three-valued logic here to make the answer complete. Anyway, +1 :) – Michał Turczyn Mar 30 '19 at 09:46

score 0 · Answer 2 · edited Mar 30 '19 at 09:44

0

change your query to

SELECT 
  * 
FROM
  `tc_wallet` 
WHERE login_id = 'gaurav.wakharkar' 
  AND `delete_flag` = 'F' 
  AND (`tips_type` != 'Collection' or `tips_type` is null)

edited Mar 30 '19 at 09:44

MatBailie

83,401
18
103
137

answered Mar 30 '19 at 09:40

Abdul Rasak

1
2

Difference between (NULL) and empty in mysql column values

2 Answers2