SQL Where Clause with in IF Clause

Question

Let's say :DeptID is a parameter passed to the SQL query and if @DeptID is null, then show all rows, else filter the result by DeptID.

Some pseudo code like

SELECT * 
FROM EMPLOYEE 
   IF :DeptID not = null 
     WHERE DeptID = :DeptID
   ELSE (no filter)

Looking for correct syntax suitable for PostgreSQL & MySQL

The expected result is: when the parameter DeptID passed in is NULL, then show all employees.

And when the parameter DeptID is passed as 10, show employees for the department with an ID of 10.

`where DeptID = :DeptID or :DeptID is null ` – jarlh Dec 05 '19 at 14:31 — jarlh, Dec 05 '19 at 14:31

score 1 · Accepted Answer · answered Dec 05 '19 at 14:31

1

SELECT * 
FROM EMPLOYEE 
WHERE :DeptID is null 
   OR :DeptID = DeptID

answered Dec 05 '19 at 14:31

juergen d

The Expected result is , when the parameter DeptID passed is Null then show all employees. And When the Parameter DeptID is passed as 10, show employees for that deptarment ID which is 10. – user2458922 Dec 05 '19 at 14:39
Yes, that is what the code does. Didn't you test it? – juergen d Dec 05 '19 at 14:40

1 Answers1