Oracle – How to Exclude Rows Containing Null Values

caseoracle

This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes NULL values. How would the below query be constructed to exclude rows containing NULL values within the column alias?

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS "NewSalutation"
FROM student.student

I welcome alternative approaches-thanks!

Best Answer

SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
) 
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.

Related Question