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

Question

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.

@Nick i want a query where if my condition parameter is null then that part of the where condition should be ignored and rest of the not null parameter should be considered. — Punam Goswami
– Punam Goswami, Commented Aug 12, 2019 at 11:16

Tim Biegeleisen · Accepted Answer · 2019-08-12 10:50:59Z

2

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.

answered Aug 12, 2019 at 10:50

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Barmar · Accepted Answer · 2024-12-20 23:09:30Z

0

You can use the IF() function:

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

answered Dec 20, 2024 at 23:09

Barmar

789k57 gold badges554 silver badges669 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related