CODEWITHSUNDEEP
mysqlsqlnull
Sahil Sharma
Sahil Sharma

Reputation: 4217

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?

Upvotes: 0

Views: 158

Answers (4)

Bohemian
Bohemian

Reputation: 424993

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)

Upvotes: 1

Vadivel S
Vadivel S

Reputation: 660

Try this query

select id, name from customers where CASE WHEN id_from_input<>null THEN id=id_from_input ELSE id END

Upvotes: 0

Blank
Blank

Reputation: 12378

Try to use or:

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

if id_from_input is null then or clause will not be calculated.

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use CASE Statement to achieve your result : 

SELECT id, name FROM customers WHERE id = CASE WHEN ISNULL(id_from_input,'') 
<> '' THEN id_from_input ELSE id END

Upvotes: 1

Related Questions