This is v1.4.x to be clear.
I have recently found a limitation in the Magento-generated query for products, cited in this thread:
On that subject, I started searching for a way to pass a complete SQL query to the model, but have not found any way listed. Is this possible? Again, this is a limited case and Magento unfortunately does an INNER JOIN with no remedy when I need a LEFT JOIN.