Return all values if parameter is NULL using SQL?

Question

I am not sure what is the right way to do this.

I basically want to return all values if value for certain filter is not defined.

Get request is made with filters like this:

url.com?filter1=abc&filter2=123&filter4=33

so as filter3was not defined i want to return all values regardless what their filter3 value is. Easy logic right.

But if I try to implement in SQL, I get stuck.

SELECT * from TABLE_NAME WHERE filter1 = $1 AND filter2 = $2 AND filter3 = $3 AND filter4 = $4 .

How can I modify SQL above to respond to undefined/blank value and return all. Or is this even correct strategy.

If you're handling the request why not just use a different SQL command? Or build the command based on the parameters you have? What are you using on the back end? — Dave Newton
– Dave Newton, Commented May 26, 2017 at 2:29
What you are suggesting could be the better way, can you \show an example. This is that something came up to mind so I am doing it. Other was use if else for every condition which doesn't scale at all. Five parameters would mean 5 if else statements. — Muhammad Umer
– Muhammad Umer, Commented May 26, 2017 at 15:23

Gordon Linoff · Accepted Answer · 2017-05-26 21:55:15Z

2

If you have indexes, it is better to build the custom SQL. But, if performance is less of an issue (say the table is small), just do the explicit comparison:

SELECT * 
FROM TABLE_NAME
WHERE ($1 IS NULL or filter1 = $1) AND
      ($2 IS NULL or filter2 = $2) AND
      ($3 IS NULL or filter3 = $3) AND
      ($4 IS NULL or filter4 = $4);

If the value is passed in as something other than NULL, then adjust the logic accordingly. For instance:

WHERE ($1 = '' or filter1 = $1) AND
      ($2 = '' or filter2 = $2) AND
      ($3 = '' or filter3 = $3) AND
      ($4 = '' or filter4 = $4);

edited May 26, 2017 at 21:55

answered May 26, 2017 at 2:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Muhammad Umer Over a year ago

usually i think it's blank, what would i put for blank string or number. In nodejs, this is the code : db.query( "SELECT * FROM TABLE WHERE C_1 = $1 AND C_2 = $2", ['test', undefined]")... not sure what undefined becomes

Collectives™ on Stack Overflow

Return all values if parameter is NULL using SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related