sqldatabaseamazon-redshift

Filter out specific value without excluding the NULL rows

I have the following table and I want to filter out a specific value but keeping the null (empty) rows.

category       sell
Apple           1
Orange          3
Banana          2
null            1
Orange          2
null            1

I tried different ways but the query kept filtering out the null rows. For example, I only want to filter out the rows with the orange, the output table should be:

category       sell
Apple           1
Banana          2
null            1
null            1

SELECT *
FROM table AS t 
WHERE t."sell"  NOT IN ('Orange')

I also tried with

WHERE t."sell"  != 'Orange'

OR

WHERE t."sell"  NOT LIKE '%Orange%'

All of the returned results exclude the rows with the null value. Any suggestions would be really appreciated.

Solution

  • If you have this schema:

    create table transaction(
  category varchar(15),
  sell int
)

insert into transaction(category, sell)
values('Apple',1), ('Orange',3), ('Banana',2), (null,1), ('Orange',2), (null,1)

    You could use:

    select *
from transaction
where category != 'Orange'
   or category is null

    Which results in:

    category    sell
Apple       1
Banana      2
null        1
null        1