Common problems with SQL
Not every developer has enough experience with building relational queries, not every performance problem is obvious when writing the SQL query, not every SQL query is executed the expected way, and not every problem manifests itself when performing sketchy testing. One of my job responsibilities at the previous position was supervising the performance of the production PostgreSQL database, and identification of problematic SQL queries. I've seen many "broken" SQL statements so let me present my personal "hit parade" in a series of articles.
Vast majority of this project (resp. principles listed in it) may be applied to all relational databases, not only PostgreSQL - basic principles hold for all relational principles without exceptions (although marketing departments of particular companies will try to convince you their developers were able to break physical laws, no doubt about that).
Nevertheless, though the principles are valid universally, the databases may differ in essential details (e.g. supported types of indexes, ability to use indexes to track NULL values, etc.), and especially in provided tools - a crucial role of SQL statement tuning plays so called "execution plan", i.e. a description of how the statement will be evaluated (what tables will be joined, how the join will be performed and in what order, what conditions will be evaluated using indexes, etc.) and the way to read and display it is specific for each database. PostgreSQL provides EXPLAIN [ANALYZE] command, Oracle fills the information into a table (which may be consequently read using traditional tools - sqlplus, TOAD, SQL Developer, ...) etc.
In this article PostgreSQL is used for demonstration purposes, and thus the EXPLAIN command is used. If you're using a different database, find out how to read and display execution plan.
This definitely is not an exhausting list of all possible problems that may be observed when writing SQL queries - these are just examples of some typical problems, classified by the original cause.
