CODEWITHSUNDEEP
sqldatabaseoracle-database
TeAyudo
TeAyudo

Reputation: 59

Oracle-SQL: Dont consider AND If WHERE clause parameter is null

I was searching for it but could not find any propiate solution for this.

SELECT a, b, c FROM table WHERE d := parameter1 AND e := parameter2;

So if d and e are given, the result query is working. d parameter is always requiered but e is optional so if there is no input for e I dont want to consider the AND e := parameter2 Statement and getting all the rows with only the d parameter.

Upvotes: 0

Views: 880

Answers (4)

LoztInSpace
LoztInSpace

Reputation: 5697

The other answers are generally correct, but you might actually be better using 2 different queries and choosing which one in the calling code. While smarter than the old days, the query plans can get "locked in" based upon the parameters used in the first invocation. If those are atypical you may get plan that is not generally the best. This behaviour will vary between DB engines (Oracle I think is quite good).

Seeing as you already know whether you have a value or not when you make the call, simply execute one bit of SQL or the other.

If you have lots of values then this can get out of hand, but then you also run a larger risk of missing out on the best execution plan.

Upvotes: 0

Gaj
Gaj

Reputation: 886

Try this

SELECT a, b, c FROM table WHERE d := parameter1 AND case when e is null then parameter2 else e end := parameter2;

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

WHERE d = parameter1 AND (e = parameter2 OR parameter2 is null)

Please be careful about = instead of :=.

Upvotes: 3

Ychdziu
Ychdziu

Reputation: 445

SELECT a, b, c FROM table WHERE d := parameter1 AND (e is not null and e := parameter2 or e is null);

Upvotes: 1

Related Questions