Add new comment

I keep coming across a construct in some legacy SQL that has been causing all kinds of performance issues for us. I guess you could call it using the IN condition with multi-column subqueries. I located the syntax for the IN condition here but it doesn't really get into much detail about using this construct.

Here is an example of the subquery (it returns the lowest salary and department_id for each department):

And here is how it is incorporated into the main query (returns all employees with the lowest salary in each department):

If you are wondering what happens behind the scenes, I found a reference in the Oracle documentation to how Oracle may handle optimizing a construct like this in the How the CBO Merges an IN Subquery section of the Tuning Guide.

So there you have it, a (old) new way to use the IN operator!

-Dave

Keywords: IN Condition , IN Operator