mysql

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.

Solution

  • 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.