How to filter null value in Oracle sql

Question

I want to filter the records that COMMENTS attribute is null

I have tried

SELECT TABLE_NAME, COMMENTS FROM 
    (SELECT TABLE_NAME, COMMENTS FROM (select * FROM user_tab_comments))
    WHERE COMMENTS != null;

But it didn't return the right output. How can I do it?

Jorge Campos · Accepted Answer · 2017-03-31 04:26:46Z

9

NULL is a state not a value, therefore you can't use normal operators on it. Use IS NULL or IS NOT NULL instead.

SELECT TABLE_NAME, COMMENTS 
  FROM (SELECT TABLE_NAME, COMMENTS FROM (select * FROM user_tab_comments))
    WHERE COMMENTS IS NOT NULL;

Just saw that you don't need all those subqueries

SELECT TABLE_NAME, COMMENTS 
  FROM user_tab_comments 
 WHERE COMMENTS IS NOT NULL;

answered Mar 31, 2017 at 4:26

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1