Simply SQL: The FROM Clause

An Application for Left Outer Joins: a Sitemap

Looking at the results of our LEFT OUTER JOIN query, it's easy enough to see how they could form the basis of a sitemap for the CMS. For example, the HTML for the sitemap that can be produced by these query results might be:

<h2>Gentle Words of Advice</h2>      
<ul>      
 <li>Be Nice to Everybody (2009-03-02)</li>      
</ul>      
   
<h2>Stories from the Id</h2>      
<ul>      
 <li>What If I Get Sick and Die? (2008-12-30)</li>      
</ul>      
   
<h2>Log On to My Blog</h2>      
   
<h2>Humorous Anecdotes</h2>      
<ul>      
 <li>Hello Statue (2009-03-17)</li>      
 <li>Uncle Karl and the Gasoline (2009-02-28)</li>      
</ul>      
   
<h2>Our Spectacular Universe</h2>      
<ul>      
 <li>The Size of Our Galaxy (2009-04-03)</li>      
</ul>

If you’re an experienced web developer, you can probably see how you’d make the transformation from query results to HTML using your particular application language.

Notice that the Log On to My Blog category has no entries, but is included in the result (because it’s a left outer join). Therefore, the application logic needs to detect this situation, and not produce the unordered list (<ul>) tags for entries in that category. Without going into the details of application programming logic, let me just say that it’s done by detecting the NULLs in the entries columns of that result row.

Outer Joins Produce NULLs

Our left outer join includes rows from the left table that have no match in the right table, as shown in Figure 3.13, “The results of the left outer join query”. So what exactly are the values in the title and created columns of the blog category result row? Remember, these columns come from the entries table.

The answer is: they are NULL.

NULL is a special value in SQL, which stands for the absence of a value. In a left outer join, columns that come from the right table for unmatched rows from the left table are NULL in the result set. This literally means that there is no value there, which makes sense because there is no matching row from the right table for that particular row of the left table.

Working with NULLs is part of daily life when it comes to working with databases. We first came across NULL (albeit briefly) in Chapter 1, An Introduction to SQL, where it was used in a sample CREATE TABLE statement and we’ll see NULL again throughout the book.

Right Outer Join: Entries and Categories

The following right outer join query produces exactly the same results as the left join query we just covered:

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

But how can this be?

Hopefully you've spotted the answer: I've switched the order of the tables! In the right outer join query, I wrote:

FROM entries RIGHT OUTER JOIN categories

In the preceding left outer join query, I had:

FROM categories LEFT OUTER JOIN entries

The lesson to be learned from this deviousness is simply that left and right outer joins are completely equivalent, it’s just a matter of which table is the outer table: the one which will have all of its rows included in the result set. Because of this, many practitioners avoid writing right outer queries, converting them to left outer joins instead by changing the order of the tables; that way the table from which all rows are to be returned is always on the left. Left outer joins seem to be much easier to understand than right outer joins for most people.

Right Outer Join: Categories and Entries

What if I hadn’t switched the order of the tables in the preceding right outer join? Suppose the query had been:

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

This time, as in our first left outer join, the categories table is on the left, and the entries table is on the right. The following figure shows the results of this query are the same as the results from our earlier inner join.

How can this be? Is this more deviousness? No, not this time; the reason is because it’s the actual contents of the tables. Remember, a right outer join returns all rows of the right table, with or without matching rows from the left table. The entries table is the right table, but in this particular instance, every entry has a matching category. All the entries are returned, and there are no unmatched rows.

So it wasn’t really devious to show that the right outer join produces the same results as the inner join, because it emphasized the rule for outer joins that all rows from the outer table are returned, with or without matching rows, if any. In this case, there weren’t any.

To really see the right outer join in action, we’d need an entry that lacks a matching category. Let’s add an entry to the entries table, for a new category called computers, as shown in the figure below.

Trying Out Your SQL

The INSERT statement that adds this extra row to the entries table can be found in the section called “Content Management System”.

Figure 3.17, “The results of the right outer join query—take two” shows that when we re-run the right outer join query with the new category, the results are as expected.

This time, we see the unmatched entry in the query results, because there’s no row in the categories table for the computers category.

Full Outer Join: Categories and Entries

Our next example join query is the full outer join. The full outer join query syntax, as I’m sure you can predict, is remarkably similar to the other join types we’ve seen so far:

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

This time, the join keywords are FULL OUTER JOIN, but an unfortunate error happens in at least one common database system. In MySQL, which doesn’t support FULL OUTER JOIN despite it being standard SQL, the result is a syntax error: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN entries ON …'

The figure below shows the result in other database systems that do support FULL OUTER JOIN.

Notice that the result set includes unmatched rows from both the left and the right tables. This is the distinguishing feature of full outer joins that we saw earlier; both tables are outer tables, so unmatched rows from both are included. It’s for this reason that full outer joins are rare in web development as there are few situations that call for them. In contrast, inner joins and left outer joins are quite common.