I’ve a table where we have a column ‘column_name’ of type char(size 6). I wrote a query like below
select * from table_name where column_name != "String"
I was hoping to get the tuples in table which has values other than ‘String’ for column ‘column_name’ including tuples which has NULL value for column ‘column_name’. But this query is not returning tuples having NULL value for column ‘column_name’. Could you please help me understand why this is happening? And should my query be like this if I also want to have tuples with NULL values for column ‘column_name’ or is there any better way?
select * from table_name where ('column_name' != "String" or 'column_name' is NULL)
!= returns null when either or both operands are null.
You can use the null-safe equals operator
<=> (inversed, because you want not equals):
not(column_name <=> 'String')
<=> will return 0, not null, when column_name is null and the right operand is not null.