Possible Duplicate:
Hidden Features of SQL Server
What are those pro/subtle techniques that SQL provides and not many know about which also cut code and improve performance?
eg: I have just learned how to use CASE statements inside aggregate functions and it totally changed my approach on things.
Are there others?
UPDATE: Basically any vendor. But PostgreSQL if you want to focus only on one :D
under MySQL, using the keyword "STRAIGHT_JOIN". If you know your data, and the relationships of lookup tables that you are joining to, sometimes the optimizer looks at the smaller tables as a basis of a join and tries to query the "less record" count to your "bigger" table thus taking significantly more time. If your primary table is first in the "from", and its "criteria" up front, the straight join will hit that first, join to the rest of the tables and be done in no time.
I've had to do this dealing with gov't data of 10+ million records joined to about 15+ lookup tables. Without straight-join, the system choked after 20+ hours. Adding Straight-join, it was done in about 2 hrs.
Derived tables to create "variables" and reduce repeated code.
Something like this but can be expanded upon. Obviously "Average Value" can be a much more complex calculation, and if you have several it helps clean up code.
Select *, case when AverageValue > 50 then 'Pass' Else 'Fail' end
From
(
Select ColA, ColB, AverageValue = (ColA+ColB)/2
From InnerMostTable
) AverageValues
Order By AverageValue Desc