Using CASE WHEN Oracle SQL

An example demonstrate using CASE WHEN in Oracle Sql.

CREATE TABLE DEPT(
DEPT_ID NUMBER,
DEPT_NAME VARCHAR2(100));

ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID);

CREATE TABLE EMP(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER);


ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID);

ALTER TABLE EMP ADD CONSTRAINT EMP_FK FOREIGN KEY (DEPT_ID)
REFERENCES DEPT(DEPT_ID);


INSERT INTO DEPT VALUES(1,'Human Resource');

INSERT INTO EMP VALUES(100,'Employee One',1);

INSERT INTO EMP VALUES(101,'Employee Two',null);

COMMIT;

SELECT A.EMP_ID,A.EMP_NAME,A.DEPT_ID,
CASE
WHEN A.DEPT_ID IS NOT NULL THEN
(SELECT DEPT_NAME FROM DEPT WHERE DEPT_ID=A.DEPT_ID)
ELSE
''
END DEPT_NAME
FROM EMP A