CODEWITHSUNDEEP
mysqlsqlstringwhere-clausesql-null
Ajinkya Karode
Ajinkya Karode

Reputation: 197

Difference between (NULL) and empty in mysql column values

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

  1. Collection,Payable
  2. '' (empty)
  3. 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 ?

Upvotes: 3

Views: 187

Answers (2)

Abdul Rasak
Abdul Rasak

Reputation: 1

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)

Upvotes: 0

GMB
GMB

Reputation: 222472

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

Upvotes: 3

Related Questions