MVSFORUMS.com

[satyenderd]

Hi all,
Could anyone please tell me if a Join query will take more time to get the data or a nested query.

Thanks in advance.
_________________
Satya

[kolusu]

satyenderd,

Joins should always be faster - theoretically and realistically. Subqueries particularly correlated can be very difficult to optimise. If you think about it you will see why - technically, the subquery could be executed once for each row of the outer query

Subqueries such as that described are one instance of the way that SQL implements relational calculus (you will see that it is basically an "Exists" type of
operation). Joins are an implementation of relational algebra. The optimisation
of relational algebraic operations is *very* well understood, while the calculus
is much more difficult to optimise.

Realistically, most good DBMSs will optimise a query such as yours to use a join instead, thus converting the implementation from calculus to algebra. In general, subqueries - particularly correlated - should be avoided unless absolutely necessary. It makes the query harder to read/maintain, pushes more work onto the server, and is generally just a far less appropriate style of SQL.

Kolusu

[satyenderd]

Thanks Kolusu.
_________________
Satya