Summary

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

We discussed the issues of NULL and three-valued logic, and you learned why you must always consider these issues when you're 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 for doing JOIN operations, and I 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 extensions to the standard GROUP BY clause for use with aggregate functions, and we discussed some tips and techniques for using these capabilities.

Although this chapter is far from a complete treatise on the SQL language or 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.