Return all rows if null value is passed

Question

I have a query like this:

select empno,name
from emp
where job = 'CLERK'
and empno = :empno

If I pass empno that is null I would like to display all the records that match the condition of job = 'CLERK'. If empno is a specific number then it should filter for job and empno.

Anyway to do this in SQL without using PLSQL?

Answer 1

Something like this if pass parameter is null than replace it with the actual column value ...

select empno,name from emp where 
job = 'CLERK' 
and empno = NVL(:empno ,empno)

How NVL work

The syntax for the NVL function is:

NVL( string1, replace_with )

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

Answer 2

and (empno = :empno or :empno is null)