CODEWITHSUNDEEP
mysql
Alexandr Shaulskyi
Alexandr Shaulskyi

Reputation: 25

Ignore parameter in WHERE clause if it is set to null

I have a table which looks like this:

id c1 c2 c3 c4
1  2  3  4  5
2  2  4  4  5

I have a query which selects an object from table based on values in columns:

SELECT * FROM table WHERE c1= '2' and c2 = '3' and c3 = '4' and c4 = '5'

How would I ignore a part of WHERE condition if it is set to null?

For example if I'd query like:

SELECT * FROM table WHERE c1='2' and c2= null and c3 = '4' and c4 = '5'

I'd get both objects from table. Thank you.

Upvotes: 0

Views: 1192

Answers (2)

forpas
forpas

Reputation: 164089

Since there are not any nulls in your columns you can use COALESCE() like this:

SELECT * 
FROM tablename 
WHERE c1 = COALESCE(@p1, c1) 
  AND c2 = COALESCE(@p2, c2)
  AND c3 = COALESCE(@p3, c3)
  AND c4 = COALESCE(@p4, c4)

Upvotes: 1

Karthik Radhakrishnan
Karthik Radhakrishnan

Reputation: 944

if you want to completely ignore the null, you can check with in operator. you can use the COALESCE function.

SELECT * FROM table WHERE COALESCE(c1, '2') = '2' and COALESCE(c2,'3')='3' and COALESCE(c3, '4') = '4' and COALESCE(c4, '5') = '5'

This will match the c1 column for value 2 and nulls. in effect ignore any nulls.

However if you just want c2 to be ignored just use the coalesce on c2 alone

Upvotes: 1

Related Questions