CASE WHEN EXISTS in WHERE clause-CodePudding

I want to print all employee names and also if the employee is present in a table.

EMP_ID	ENAME
1	ALLEN
2	MAX
3	BEN

EMP_ID	EC_CODE
1	CONFIG_1
2	CONFIG_2
3	CONFIG_1

Query:

SELECT 
    ename, 
    (CASE 
         WHEN EXISTS (SELECT 1 FROM m_emp_config ec 
                      WHERE ec_code = 'CONFIG_1' AND emp_id = emp.emp_id) 
             THEN 'Y' 
             ELSE 'N' 
     END) config
FROM 
    emp emp

Can we write the CASE WHEN EXISTS in the WHERE clause instead of there?

I am new to SQL, please help me.

Expected output for the SQL statement:

ENAME	CONFIG
ALLEN	Y
MAX	N
BEN	Y

CodePudding user response：

Your current query is correct for doing this via exists. Here is an alternative version using a left join:

SELECT DISTINCT
    e.ENAME,
    CASE WHEN ec.EMP_ID IS NOT NULL THEN 'Y' ELSE 'N' END AS CONFIG
FROM emp e
LEFT JOIN m_sys.m_emp_config ec
    ON ec.EMP_ID = e.EMP_ID AND
       ec.ec_code = 'CONFIG_1'
ORDER BY e.EMP_ID;

CodePudding user response：

You can also use DECODE instead of a CASE WHEN construct.

SELECT 
e.ename, 
DECODE(ec.ec_code,NULL, 'N','Y') AS config
FROM emp e
LEFT JOIN m_emp_config ec
ON ec.emp_id = e.emp_id AND
ec.ec_code = 'Config_1'
ORDER BY e.emp_id;