To find out the SQL query Magento uses to retrieve a specific collection, just do the following:

Let's say you would like to know what query is used to get a product collection - go to your debugging area (or maybe some phtml file), and paste following code:

$_productCollection = Mage::getResourceModel("catalog/product_collection");
$_productCollection->load(true);

Passing "true" as a parameter to the load function, will print out the query that Magento uses to retrieve the items for the collection.

Lets observe at the following example:

For some reason you want to show the products whose price is lower than 100$.
If you go to list.phtml at app/design/frontend/base/default/template/catalog/product, you will see the following line:

$_productCollection=$this->getLoadedProductCollection();

$_productCollection is instance of Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection, and of course this is already loaded collection (collection that has items). resource collections in Magento - once the collection is loaded, you cannot add anything to the "select" and then call "load" again - this will not change the items of a collection. The solution is:

$_productCollection->clear();
$_productCollection->getSelect()->where('price_index.price < 100'); $_productCollection->load();

In line 1 we called to "clear" function that deleted all items in the collection, but preserved the current select. So in the line 2 we added stuff to the select, and called "load". This gave us the desired result.