CODEWITHSUNDEEP
sqlsql-serveroracle-database
dougp
dougp

Reputation: 3087

Can a correlated subquery be replaced by an inner join?

Given that I'm asking about a small subset of possible uses of a correlated subquery...

I'm working with a vendor product that uses a lot of correlated subqueries. My task is to modifiy the queries to make them meet our business needs. The vendor's queries appear to be overly complicated and difficult to maintain.

One simple example...

select e.EmployeeLastName
, e.EmployeeFirstName
, e.EmployeeRecordEffectiveDate
, e.EmployeeRecordEndDate

from Employee e

where e.EmployeeLastName like 'a%'
  and exists (
       select 1
       from Position p
       where p.PositionId = e.EmployeePositionID
  )

order by e.EmployeeRecordEffectiveDate
;

...can be replaced by a simpler query using an inner join...

select e.EmployeeLastName
, e.EmployeeFirstName
, e.EmployeeRecordEffectiveDate
, e.EmployeeRecordEndDate

from Employee e
  inner join Position p on p.PositionId = e.EmployeePositionID

where e.EmployeeLastName like 'a%'

order by e.EmployeeRecordEffectiveDate
;

Of course, most of the queries are more complicated. For example, replace the Position table with a subquery - but keep the main structure.

Are there risks involved with making the queries simpler? (maybe involving NULLs...)
Is there an advantage to using a correlated subquery?

Upvotes: 2

Views: 976

Answers (2)

Andrew Simon
Andrew Simon

Reputation: 158

I would compare the execution plans between the queries that have correlated subqueries and those that have them replaced with a join. While in many cases correlated subqueries can lead to performance issues if not written properly, there are others where they can actually perform better. For example, a NOT EXISTS subquery usually performs better than a LEFT JOIN ... WHERE [column] IS NULL, while EXISTS subqueries typically perform the same as simple INNER joins.

With that being said, you should compare the performance differences on an individual basis by replacing the correlated subqueries with joins one-by-one.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Assuming that Position(PositionId) is unique, then your two queries are equivalent.

I'm not sure that you will get any performance benefit. EXISTS is usually fine from that perspective.

Upvotes: 0

Related Questions