The mighty Transact-SQL SELECT

You probably already know that the most important SQL statement is the mighty SELECT statement you use to retrieve data from your databases. Every database developer knows the basic clauses and their usage:

• SELECT to define the columns returned, or a projection of all table columns

• FROM to list the tables used in the query and how they are associated, or joined

• WHERE to filter the data to return only the rows that satisfy the condition in the predicate

• GROUP BY to define the groups over which the data is aggregated

• HAVING to filter the data after the grouping with conditions that refer to aggregations

• ORDER BY to sort the rows returned to the client application

Besides these basic clauses, SELECT offers a variety of advanced possibilities as well. These advanced techniques are unfortunately less exploited by developers, although they are really powerful and efficient. Therefore, I advise you to review them and potentially use them in your applications. The advanced...