SQL Performance Tuning

Chapter 6. Subqueries There seems to be a Law of DBMS Development that states—In version 1.0 the subquery support will be bad or nonexistent, and it will slowly get better. Witness the IBM texts that tell you how bad subqueries were in the ...

Refresher A subquery is a SELECT statement with another SELECT inside it. It looks like this: SELECT /* outer or parent query */ (SELECT ) /* subquery, inner query or subselect */ There are three possib...

Join versus Subquery When you write a join, you're letting the DBMS optimizer decide among a variety of plans, such as merge-sort, nested-loop, or hash; see Chapter 5, "Joins." But when you write a subquery, you are strongly hinting that you...

Syntax Choices You can make several syntax choices when writing subqueries. The major subquery predicates are IN and EXISTS, but we'll also look at negation, duplicate elimination, double subqueries, and set operation alternatives. IN I...

Parting Shots When you use subqueries, you are giving the DBMS a weak "hint" about processing plans, but the DBMS still has plenty of scope for decision making. Each of the possible plans may be best for certain situations so a blanket state...