SQL - How does one implement IF-THEN-ELSE logic in a SELECT statement?

create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);

select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );

create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);


How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;

Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;

Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;

NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:

SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:

SELECT NULLIF(ename, ename) FROM emp;


--dont forget to create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);

select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );

create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);


How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;

Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;

Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test

NVL and NVL2 can be used to test for NULL values.

NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;

NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;

COALESCE() Function

COALESCE() returns the first expression that is not null. Example:

SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;

NULLIF() Function

NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:

SELECT NULLIF(ename, ename) FROM emp;


Dont forget to Drop Table YOUR_TABLE after you are done doing this example.