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 stackoverflow.com/questions/1106258/mysql-null-vs — shushu304
– shushu304, Commented Mar 30, 2019 at 9:38
Typing mysql null empty string into any search engine would have answered that for you. — MatBailie
– MatBailie, Commented Mar 30, 2019 at 9:45

GMB · Accepted Answer · 2019-03-30 09:40:25Z

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, 2019 at 9:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michał Turczyn Over a year ago

You should mention three-valued logic here to make the answer complete. Anyway, +1 :)

MatBailie · Accepted Answer · 2019-03-30 09:44:21Z

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, 2019 at 9:44

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

answered Mar 30, 2019 at 9:40

Abdul Rasak

12 bronze badges

Collectives™ on Stack Overflow

Difference between (NULL) and empty in mysql column values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related