Conditional WHERE EXISTS in Oracle query

Question

Oracle here. I have the following table:

create table Foobars ( 
    id integer constraint pkFoobars primary key using index,
    fizz varchar2(20 char), 
    buzz varchar2(20 char)
)

In my code I will be given a string called fizzOrBuzz, and I will not know which field (fizz or buzz) it will be ahead of time. I need to search the Foobars table for either a matching fizz or buzz based on the fizzOrBuzz value. The only thing that is certain is that there are no duplicates (no 2 records will have the same fizz value, and no 2 records will have the same buzz value).

My query thus far:

SELECT
  id
FROM
  Foobars
WHERE EXISTS (
  SELECT
    id
  FROM
    Foobars
  WHERE
    fizz = ? -- fizzOrBuzz gets injected here by the app layer
) OR (
  SELECT
    id
  FROM
    Foobars
  WHERE
    buzz = ? -- fizzOrBuzz gets injected here by the app layer  
)

However this isn't valid SQL code (doesn't execute) and I'm sure there's a better way of doing this altogether. Any ideas?

OR => OR EXISTS ? But the other options offered as answers are be better; not least because your subqueries aren't correlated - assuming you're expecting the ID from those to match the main query. (It's helpful to show the error you got, and sample data/expected results.) — Alex Poole
– Alex Poole, Commented Aug 16, 2019 at 15:03

Gordon Linoff · Accepted Answer · 2019-08-16 15:03:13Z

6

Why not just use OR?

select id
from foobars
where fizz = ? or buzz = ?;

Or, just use one parameter with IN:

select id
from foobars
where ? in (fizz, buzz);

answered Aug 16, 2019 at 15:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Chris Saxon · Accepted Answer · 2019-08-16 15:02:56Z

4

I'm not sure why you need exists, surely a basic or works here:

select id
from   foobars
where  fizz = ? -- fizzOrBuzz gets injected here by the app layer
or     buzz = ? -- fizzOrBuzz gets injected here by the app layer

answered Aug 16, 2019 at 15:02

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Comments

Lukasz Szozda · Accepted Answer · 2019-08-16 15:01:20Z

2

You could use UNION:

SELECT id FROM Foobars WHERE fizz = ?
UNION 
SELECT id FROM Foobars WHERE buzz = ?

answered Aug 16, 2019 at 15:01

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

5 Comments

Dr Y Wit Over a year ago

Writing query in this manner in Oracle does not make sense. You might want to read about Oracle query transformations and OR-expansion in particular.

Gordon Linoff Over a year ago

@DrYWit . . . You should be a little more informative with such a comment. Is the issue the UNION? Would it be fixed by using UNION ALL? Are you assuming there are indexes on the fizz and buzz columns?

Lukasz Szozda Over a year ago

@DrYWit The query itself is perfectly valid and I know what a OR-expansion is. Please note I am using UNION and not UNION ALL

Dr Y Wit Over a year ago

@GordonLinoff, if someone uses OR then CBO can transform a query to the form which is more efficient based on statistics, presence of indexes etc. With UNION Oracle will always access table twice; not to mention that, yes, UNION ALL must be used instead of UNION. And for those who are interested: blogs.oracle.com/optimizer/…

Dr Y Wit Over a year ago

@LukaszSzozda, the query you provide does not have any advantage in compare to version with 'OR' but has a number of drawbacks instead and should never be recommended even though it's "perfectly valid". I hope it's clear.

Popeye · Accepted Answer · 2019-08-16 15:03:29Z

1

You can simply use OR

SELECT id FROM Foobars WHERE fizz = ? OR buzz = ?

Cheers!!

answered Aug 16, 2019 at 15:03

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Conditional WHERE EXISTS in Oracle query

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related