In SQL, how can I effectively add a conditional WHERE condition depending on if a particular value i-CodePudding

Let’s say I have a users table and I want to query that table based on several attributes like first name, last name, age, state, etc.

I’d like to do this in a single query that works whether values for these parameters are NULL or not. If a particular parameter is NULL, then the WHERE for that column should basically just turn into a WHERE 1=1 so that it doesn’t affect the results.

This way, a single query can conditionally apply several filters, e.g.:

SELECT *
FROM users
WHERE
  — filter on first_name only if $1 is NOT NULL
  — filter on last_name only if $2 is NOT NULL
  — etc
;

I’m using a Postgres client in NodeJS where parameters are passed like so:

execSql({
  text: ‘SELECT * FROM users WHERE first_name = $1’,
  values: [firstName]
})

How can I modify that query to only filter when a value for firstName is supplied, using only combinations of AND, OR, COALESCE and NULLIF?

CodePudding user response：

This assumes the parameter will be null if its "not passed in".

SELECT * 
  FROM users 
 WHERE coalesce(first_name,'') = coalesce($1,first_name,'')

If $1 is null and first_name is not null, this will compare first_name = first_name (True)

If $1 is not null and first_name is null, $1 = Null (false)

If both $1 and first_name are null, '' = '' (true)

Odd behavior

If first_name is null and $1 is empty string, '' = '' (true). May not be what you want.