Summary

This chapter examined the SELECT statement, the hallmark of the SQL language. Although we presented a fairly well-rounded treatment of SELECT, instead of focusing on basic queries, we looked at more subtle issues and SQL Server_specific capabilities.

We saw the issues of NULL and three-valued logic and learned why you must always consider these issues while you're properly formulating queries. In three-valued logic, an answer is TRUE, FALSE, or Unknown. If you must work with NULL values but you don't understand three-valued logic, you'll introduce bugs . You must fully understand three-valued logic or structure the database to avoid the use of NULL.

You must also understand NULL when doing JOIN operations, and we discussed issues concerning OUTER JOIN with several examples. Unlike a JOIN based on equality, the JOIN order for an OUTER JOIN is vitally important. We looked at search expressions such as LIKE and the issues of trailing blanks. We examined aggregate functions, again with a discussion of how you must understand NULL for proper use of these functions.

Finally, we looked at the CUBE and ROLLUP OLAP extensions to the standard GROUP BY clause for use with aggregate functions and considered some pragmatic tips and techniques for using these capabilities.

Although this chapter is far from a complete treatise on the SQL language, or on all SQL Server's capabilities, it demonstrates how to use SQL Server facilities and extensions to help you solve real-world problems and write better applications.