Chapter 7. Pulling apart the FROM clause
When can you ever have a serious query that doesn’t involve more than one table? Normalization is a wonderful thing and helps ensure that our databases have important characteristics such as integrity. But it also means using JOINs, because it’s unlikely that all the data we need to solve our problem occurs in a single table. Almost always, our FROM clause contains several tables.
In this chapter, I’ll explain some of the deeper, less understood aspects of the FROM clause. I’ll start with some of the basics, to make sure we’re all on the same page. You’re welcome to skip ahead if you’re familiar with the finer points of INNER, OUTER, and CROSS. I’m often amazed by the fact that developers the world over understand how to write a multi-table query, and yet few understand what they’re asking with such a query.
Without JOINs, our FROM clause is incredibly simple. Assuming that we’re using only tables (rather than other constructs such as subqueries or functions), JOINs are one of the few ways that we can make our query more complicated. JOINs are part of day one in most database courses, and every day that follows is full of them. They are the simplest mechanism to allow access to data within other tables. We’ll cover the three types of JOINs in the sections that follow.