Complex SQL Queries - Evaluation of Filtering Conditions

(Page 3 of 4 )

The very first questions to consider when writing a SQL statement are:

1. What data is required, and from which tables?
   
2. What input values will we pass to the DBMS engine?
   
3. What are the filtering conditions that allow us to discard unwanted rows?

Be aware, however, that some data (principally data used for joining tables) may be stored redundantly in several tables. A requirement to return values known to be held in the primary key of a given table doesn’t necessarily mean that this table must appear in thefromclause, since this primary key may well appear as the foreign key of another table from which we also need the data.

Even before writing a query, we should rank the filtering conditions. The really efficient ones (of which there may be several, and which may apply to different tables) will drive the query, and the inefficient ones will come as icing on the cake. What is the criterion that defines an efficient filter? Primarily, one that allows us to cut down the volume of the data we have to deal with as fast as possible. And here we must pay a lot of attention to the way we write; the following subsections work through a simple example to illustrate my point.

Next: Buyers of Batmobiles >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Complex SQL Queries

· This article is an excerpt from "The Art of SQL," published by O'Reilly. We hope you...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of The Art of SQL, written by Stephane Faroult and Peter Robson (O'Reilly; ISBN: 0596008945). Check it out today at your favorite bookstore. Buy this book now.