I have the following table and I want to filter out a specific value but keeping the null (empty) rows.
x
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.
Advertisement
Answer
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