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.
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