31271 Lecture Notes - Lecture 10: Embedded Sql, Aggregate Function
Lecture 10: SQL IV
• Subqueries can be noncorrelated (executed once for the entire outer query), or they can be
correlated (executed once for each row which is returned by the outer query).
• A correlated subquery will refer back to elements found from the outer query:
• Whereas non correlated subqueries do not depend on the data from the outer query.
• A correlated subquery can use the EXISTS operator.
o The EXISTS operator returns TRUE if the subquery resulted in a non-empty set.
• Particularly for correlated subqueries it is imperative that the tables have aliases to refer to
from inside the subquery.
Subquery in From Clause
• You can have a subquery in the From clause. By calculating an aggregate function within the
FROM clause/subquery you can use this within the following WHERE clause which would not
initially be allowed to have an aggregate function.
Query Efficiency
• When using a select clause, specify the attributes you wish to return rather than using
SELECT* as this reduces network traffic.
• Subqueries affect efficiency, so try to keep everything to a limited number of queries.
Better Query Design
• Write simple queries, and break complex ones into multiple simple parts.
• Don’t nest ueies
• If possible, avoid self joins
• Retrieve only the data that you need.
find more resources at oneclass.com
find more resources at oneclass.com
