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

Question

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.

Is column e defined as NOT NULL in the table? If it is, you can use and e = nvl(parameter2,e). — William Robertson
– William Robertson, Commented Jul 4, 2018 at 8:35

Aleksej · Accepted Answer · 2018-07-04 08:28:05Z

3

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

Please be careful about = instead of :=.

answered Jul 4, 2018 at 8:28

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

Comments

Ychdziu · Accepted Answer · 2018-07-04 08:19:53Z

1

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

answered Jul 4, 2018 at 8:19

Ychdziu

4455 silver badges12 bronze badges

2 Comments

Ychdziu Over a year ago

What does not work? Can you provide sample data with expected result?

Rohit Gulati Over a year ago

This will not work as it will consider when value of e is null but he dont want to consider e condition at all.

Gaj · Accepted Answer · 2018-07-04 08:42:41Z

0

Try this

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

answered Jul 4, 2018 at 8:42

Gaj

8865 silver badges5 bronze badges

Comments

LoztInSpace · Accepted Answer · 2018-07-04 08:48:53Z

0

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.

answered Jul 4, 2018 at 8:48

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related