CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloperddlplsqldeveloper
smriti
smriti

Reputation: 33

CASE WHEN EXISTS in WHERE clause

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

Upvotes: 2

Views: 126

Answers (3)

Ramesh Kumar
Ramesh Kumar

Reputation: 1

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;

Upvotes: 0

Jonas Metzler
Jonas Metzler

Reputation: 5975

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;

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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;

Upvotes: 2

Related Questions