Understand the table relationships – most will be one to many. Know the “many” table. Identify the fields required for your joins.
Think about LEFT join scenarios – Select all the employees and their paycheck from last month. What if they didn’t get a paycheck last month?
Know the result set: 1) In a spread sheet, manually enter at least one correct record for your query. 2) Write the query in a simple enough form to identify how many records should be returned. Use both of these to test your query to make sure joining a new table doesn’t alter the result.
Break up your query into managable parts – You don’t have to write it all at once. Complex queries can sometimes just be a collection of simple queries.
Beware of mixed levels of aggregation: If you have to put: Monthly, Quarterly and Year to Date values in the same result set, you’ll need to calculate them separately in queries grouped at on different values.
Know when to UNION Sometimes it’s easier to break up sub groups into their own select statement. If you have a table mixed with managers and other employees, and on each column having to do Case statements based on membership in one of these groups, it may be easier to write a Manager query and union to an Employee query. Each one would contain their own logic. Having to include items from different tables in different rows is an obvious use.
Complex/Nested formulas – try to consistently indent and don’t be afraid to use multiple lines. “CASE WHEN CASE WHEN CASE WHEN” will drive you nuts. Take the time to think these through. Save the complex calcs for last. Get the correct records selected first. Then you attack complex formulas knowing you’re working with the right values. Seeing the values used in the formulas will help you spot areas where you have to account for NULL values and where to handle the divide by zero error.
Test often as you add new tables to make sure you’re still getting the desired result set and knowing which join or clause is the culprit.
Focus more on set theory.