How to exclude rows containing null values within columns?

Question

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!

Answer 1

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.