Simply SQL: The FROM Clause

All Columns Are Available after a Join

In any join, all columns of the tables being joined are available to the SELECT query, even if they’re not used by the query. Let's look at our inner join again:

SELECT    
 categories.name    
, entries.title    
, entries.created    
FROM    
 categories    
   INNER JOIN entries    
     ON entries.category = categories.category

In most join queries, tables being joined usually contain more columns than those mentioned in the SELECT clause. This is true here too; the entries table has other columns not mentioned in the query. We haven’t included them in Figure 3.11, “The inner join in detail” just to keep the figure simple. Although the figure is correct, it could be construed as slightly misleading, because it shows only the result set of the query, rather than the tabular structure produced by the inner join.

The figure below expands on the actual processing of the query and shows the tabular structure that’s produced by the FROM clause and the inner join; it includes the two category columns—one from each table. This tabular structure, the intermediate table, is produced by the database system as it performs the join, and held temporarily for the SELECT clause.

When a Join is Executed in a Query

Two important points come out of the analysis of our first example join query:

• A join produces an intermediate tabular result set;
• The SELECT clause occurs after the FROM clause and operates on the intermediate result set.

At the beginning of this chapter, I mentioned that the FROM clause is the first clause that the database system parses when we submit a query. If there are no syntax errors, the database system goes ahead and executes the query. Well, it turns out that the FROM clause is the first clause that the database system executes, too.

You could consider the execution of a join query as working in the following manner. First, the database system produces an intermediate tabular result set based on the join specified in the FROM clause. This contains all the columns from both tables. Then the database system uses the SELECT clause to select only the specified columns from this intermediate result set, and extracts them into the final tabular structure that is returned as the result of the query.

Qualifying Column Names

Finally, let’s take one more look at our inner join query:

SELECT    
 categories.name    
, entries.title    
, entries.created    
FROM    
 categories    
   INNER JOIN entries    
     ON entries.category = categories.category

Each of the column names used in this query is qualified by its table name, using dot notation, where the table name precedes the column name with a dot between them.

Qualifying column names is mandatory when there is more than one instance of the same column name in a query. (These would be from different tables, of course; more than one instance of the same column name in a single table is not possible, as all columns within a table must each have unique names.) If you don’t uniquely identify each of the columns that have the same name but are in different tables, you will receive a syntax error about ambiguous names. This applies whether the query makes reference to both columns or not; every single reference must be qualified.

When there is only one instance of the column name in the query, then qualifying column names becomes optional. Thus, we could have written the following and be returned the same result set:

SELECT    
 name    
, title    
, created    
FROM    
 categories    
   INNER JOIN entries    
     ON entries.category = categories.category

However, it’s a good idea to qualify all column names in this situation because when you look at the SELECT clause, you can’t always tell which table each column comes from. This can be especially frustrating if you’re only remotely familiar with the tables involved in the query, such as when you’re troubleshooting a query written by another person (or even by yourself, a few months ago).

Always Qualify Every Column in a Join Query

Even though some or even all columns may not need to be qualified within a join query, qualifying every column in a multi-table query is part of good SQL coding style, because it makes the query easier for us to understand.

In a way, qualifying column names makes the query self-documenting: it makes it obvious what the query is doing so that it’s easier to explain in documentation.

Table Aliases

Another way to qualify column names is by using table aliases. A table alias is an alternate name assigned to a table in the query. In practice, a table alias is often shorter than the table name. For example, here’s the same inner join using table aliases:

SELECT    
 cat.name    
, ent.title    
, ent.created      
FROM    
 categories AS cat    
   INNER JOIN entries AS ent    
     ON ent.category = cat.category

Here, the categories table has been assigned the alias cat, and the entries table has been assigned the alias ent. You’re free to choose any names you wish; the table aliases are temporary, and are valid only for the duration of the query. Some people like to use single letters as table aliases when possible, because it reduces the number of characters in the query and so makes it easier to read.

The only caveat in using table aliases is that once you have assigned an alias to a table, you can no longer use the table name to qualify its columns in that query; you must use the alias name consistently throughout the query. Once the query is complete however, you're free to refer to the original table by its full name again, the same alias, or even a different alias; the point here being that a table alias is defined only for the duration of the query that contains it.

Left Outer Join: Categories and Entries

Continuing our look at join queries, the left outer join query we’ll examine is exactly the same as the inner join query we just covered, except that it uses LEFT OUTER JOIN as the join keywords:

SELECT    
 categories.name    
, entries.title    
, entries.created      
FROM    
 categories    
   LEFT OUTER JOIN entries    
     ON entries.category = categories.category

The following figure shows the results of the above query.

The only difference between this left outer join query and the preceding inner join query is the inclusion of one additional row—for the category with the name Log On to My Blog—in the result set. The additional row is included because the query uses an outer join. Specifically, it’s a left outer join, and therefore all of the rows of the left table, the categories table, must be included in the results. The left table, you may recall, is simply the table that is mentioned to the left of the LEFT OUTER JOIN keywords. The figure below shows the process of the join and selection in more detail.

To make it more obvious which table is the left one and which table is the right one, we could write the join without line breaks and spacing so categories is more obviously the left table in this join:

FROM categories LEFT OUTER JOIN entries

Let’s take another look at the results of our left outer join, because there is one more important characteristic of outer joins that I need to point out.