sahil sahil -4 years ago 94
MySQL Question

My SQL: Add if condition in where statement?

I want to select all customers or one customer from same SP. Right now we are mantaining two SPs to get these details:

i.e. To get all customers:

select id, name from customers


and to get one customer:

select id, name from customers
where id=id_from_input


To make them common, i thought of pasing
id_from_input
as null to get all customers. I tried several ways to make a conditional where statement in my sql that would work with this plan, but nothing is working. For example:

select id, name from customers
where if(id_from_input <> null) id=id_from_input; end if


gives me syntax error.

How can I make a
where
clause that returns all rows when
id_from_input
is null, the matching row otherwise?

Answer Source

The expression x <> null is never true; you can only use is null, or is not null, to test for null. Correcting your attempt gives:

select id, name from customers
where id_from_input is null or id = id_from_input

Or for something more terse (and IMHO elegant):

select id, name from customers
where id = coalesce(id_from_input, id)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download