CASE WHEN EXISTS in WHERE clause

Question

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

Tim Biegeleisen · Accepted Answer · 2022-11-01T09:44:56.473

2

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;

edited Nov 01 '22 at 09:44

answered Nov 01 '22 at 09:42

Tim Biegeleisen

502,043
27
286
360

Only when there's a single row per employee in the config table. Otherwise it requires `distinct` or aggregation – astentx Nov 01 '22 at 09:44
You are right, thanks for the correction. – Tim Biegeleisen Nov 01 '22 at 09:45

score 0 · Answer 2 · answered Nov 01 '22 at 12:10

Using Decode statement

SELECT em.ename, DECODE(econ.ec_code,'Config_1', 'Y','N') AS config FROM emp em JOIN m_emp_config econ ON econ.emp_id = em.emp_id ORDER BY em.emp_id;

Using Case statement

SELECT em.ename, Case econ.ec_code when 'Config_1' then 'Y' Else 'N' END AS config FROM emp em JOIN m_emp_config econ ON econ.emp_id = em.emp_id ORDER BY em.emp_id;

Jonas Metzler · Answer 3 · 2022-11-01T12:10:17.197

-1

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

SELECT DISTINCT
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;

edited Nov 01 '22 at 12:10

answered Nov 01 '22 at 10:05

Jonas Metzler

4,517
1
5
17

As mentioned in the comment under my answer, you probably need a distinct select here. – Tim Biegeleisen Nov 01 '22 at 12:07

CASE WHEN EXISTS in WHERE clause

3 Answers3