why result shows some grade value as null-CodePudding

SELECT
    `job_id`,
    CASE `job_id`
    WHEN  'AD_PRES' THEN 'A'
    WHEN 'ST_MAN' THEN 'B'
    WHEN 'IT_PROG' THEN 'C'
    END AS 'grade'
FROM
    `employees`
WHERE
    'grade' IS NOT NULL;

CodePudding user response：

Single quotes in MySQL denote string literals. For database identifiers, such as column or table names, use double quotes, backticks, or nothing at all, if not required. Here you don't need to escape the alias. In addition, you generally cannot refer to an alias in the WHERE clause. But, MySQL has overloaded its HAVING operator to allow it to refer to an alias. Putting this all together, we can use this version:

SELECT job_id,
       CASE job_id WHEN 'AD_PRES' THEN 'A'
                   WHEN 'ST_MAN'  THEN 'B'
                   WHEN 'IT_PROG' THEN 'C'
       END AS grade
FROM employees
HAVING grade IS NOT NULL;

Note that you could also phrase this as:

SELECT job_id,
       CASE job_id WHEN 'AD_PRES' THEN 'A'
                   WHEN 'ST_MAN'  THEN 'B'
                   WHEN 'IT_PROG' THEN 'C'
       END AS grade
FROM employees
WHERE job_id IN ('AD_PRES', 'ST_MAN', 'IT_PROG');

This also works because grade would only be non NULL if the job_id were one of these three values.