MySQL parameterized SELECT query with optional column filter

Question

I have a requirement to build a versatile parameterized SELECT query which fetches data from a mysql table and optionally filters that data by values from a column in one fell swoop.

I know this can be achieved with mutltiple SELECTs, but it has to be just one statement.

The query either fetches all data if a NULL is passed or fetches filtered data if a value is passed in other than NULL.

One of the reasons for this requirement is we are externalizing our SQL strings.

I have managed to come up with this:

SELECT * FROM `table` WHERE `column` =  COALESCE(NULLIF(?,''),`column`);

I pass in a NULL when I need to fetch unfiltered data and it works smoothly, efficiently.

However, it never fetches data for which **column** is NULL.

How do I make it fetch columns with NULL data?

WHERE column IS NULL?

souslov
– souslov

2014-06-17 09:11:26 +00:00
Commented Jun 17, 2014 at 9:11 — souslov
– souslov, Commented Jun 17, 2014 at 9:11

Suvendu Shekhar Giri · Accepted Answer · 2014-06-17 13:27:32Z

2

Try this approach

SELECT * FROM `table` WHERE (? IS NULL OR `column`=?);

It works with SQL Server and this query may have few syntactical errors but I just want to convey the approach which works in SQL Sever for this kind of problems.

What it does is, at first it checks for value of the input parameter, if it is NULL then it skips the filtering and if the parameter is not NULL then only it moves to the second part of the OR statement and does the filtering.

It differs from what you are currently trying is, it checks the parameter for NULL value and not the column.

Note: the problem with your query is- when you are passing NULL, the condition becomes 'column'=null and thus it is not returning required rows. Rather we need to check columns for NULL value by 'column' IS NULL.

Please let me know if I am missing something !

edited Jun 17, 2014 at 13:27

answered Jun 17, 2014 at 9:26

Suvendu Shekhar Giri

1,4046 gold badges43 silver badges72 bronze badges

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

Collectives™ on Stack Overflow

MySQL parameterized SELECT query with optional column filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related