CODEWITHSUNDEEP
oracle-databasenull
Chandra Sekhar
Chandra Sekhar

Reputation: 16516

Any way to get null(empty) columns using IN condition in oracle? without using is null?

I have a table(employee) like below:

  EMPNO    ENAME      JOB          MGR HIREDATE        SAL       COMM     DEPTNO
  -------- ---------- --------- ------ ---------- -------- ---------- ----------
      7369 SMITH                  7902 17/12/1980      800                    20
      7499 ALLEN      SALESMAN    7698 20/02/1981     1600        300         30
      7521 WARD                   7698 22/02/1981     1250        500         30
      7566 JONES      MANAGER     7839 02/04/1981     2975                    20
      7654 MARTIN     SALESMAN    7698 28/09/1981     1250       1400         30
      7698 BLAKE                  7839 01/05/1981     2850                    30
      7782 CLARK      TESTER      7839 09/06/1981     2450                    10

Here what i need to get is:
employee number, name and job where job IN ('SALESMAN', 'MANAGER' and NULL(empty)).
I prepared a query by using is NULL

select empno, ename, job 
from employee 
where job in('SALESMAN', 'MANAGER') or job is null;

Is there any way, other than above Query?
can i pass null in side IN condition?

Upvotes: 0

Views: 160

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

use nvl function: 

select empno, ename, job 
from employee 
where nvl(job, 'NULL') in('SALESMAN', 'MANAGER', 'NULL')

Upvotes: 3

Related Questions