CODEWITHSUNDEEP
mysql
Punam Goswami
Punam Goswami

Reputation: 35

Mysql select query if condition parameter is null then ignore the parameter

I have the following logical condition:

WHERE (m.idAgent = agent OR agent is null)

It returns me all agents if I pass NULL as the agent param.

But how can I achieve the same thing in MYSQL?

That is, if I pass NULL for agent then the rest of that part of the WHERE condition (m.idAgent = agent) should be ignored and only the other conditions, not shown, which are not NULL should be considered.

Upvotes: 2

Views: 1102

Answers (2)

Barmar
Barmar

Reputation: 780871

You can use the IF() function:

WHERE IF(agent IS NULL, TRUE, m.idAgent = agent)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

You could use COALESCE as follows:

WHERE COALESCE(agent, m.idAgent) = m.idAgent

This should work because when agent is NULL, it would just be replaced with the RHS of the comparison, and therefore would always pass. I presume that the following is actually what your code looks like:

WHERE COALESCE(?, m.idAgent) = m.idAgent

Here the ? is a placeholder for a value to be bound in the statement.

Upvotes: 2

Related Questions