About the Author

Rudy Limeback

Rudy Limeback is an SQL Consultant living in Toronto, Canada. His SQL experience spans 20+ years, and includes working with DB2, SQL Server, Access, Oracle, and MySQL. He is an avid participant in discussion forums, primarily at SitePoint. His two web sites are http://r937.com/ and http://rudy.ca/.

View all articles by Rudy Limeback...

Simply SQL: The FROM Clause

This article is Chapter 3 from the SitePoint book Simply SQL by Rudy Limeback.

In Chapter 2, An Overview of the SELECT Statement, we broke the SELECT statement down into its various clauses, but looked at each clause only briefly. In this chapter, we’ll begin our more detailed look at the SELECT statement, starting with the FROM clause.

The FROM clause can be simple, and it can also be quite complex. In all cases, though, the important point about the FROM clause is that it produces a tabular structure. This tabular structure is referred to as the result set of the FROM clause. You may also see it referred to as an intermediate result set, an intermediate tabular result set, or an intermediate table. But, no matter whether the SELECT query retrieves data from one table, from many tables, or from other, similar tabular structures, the result is always the same—the FROM clause produces a tabular structure.

In this chapter we’ll review the common types of FROM clause that we might encounter in web development.

Why Start with the FROM Clause?

To begin writing a SELECT statement, my strategy is to skip over the SELECT clause for the time being, and write the FROM clause first. Eventually, we’ll need to input some expressions into the SELECT clause and we might also need to use WHERE, GROUP BY, and the other clauses too. But there are good reasons why we should always start with the FROM clause:

• If we get the FROM clause wrong, the SQL statement will always return the wrong results. It’s the FROM clause that produces the tabular structure, the starting set of data on which all other operations in a SELECT statement are performed.
• The FROM clause is the first clause that the database system looks at when it parses the SQL statement.

Parsing an SQL Statement

Whenever we send an SQL statement to the database system to be executed, the first action that the system performs is called parsing. This is how the database system examines the SQL statement to see if it has any syntax errors. First it divides the statement into its component clauses; then it examines each clause according to the syntax rules for that clause. Contrary to what we might expect, the database system parses the FROM clause first, rather than the SELECT clause.

For example, suppose we were to attempt to run the following SQL statement, in which we have misspelled teams as teans:

SELECT
 id, name
FROM
 teans  
WHERE
 conference = 'F'

In this case, the FROM clause refers to a non-existing table, so there is an immediate syntax error. If the database system were to parse the SELECT clause first, it would need to examine the table definitions of all the tables in the database, looking for one that might contain two columns called name and id. In fact, it’s quite common for a database to have several tables with two columns called name and id. Confusion could ensue and the database would require more information from us to know which table to retrieve name and id from. Hence why the database system parses the FROM clause first, and this is the first clause we think about as well.

FROM One Table

We’ve already seen the FROM clause with a single table. In Chapter 1, An Introduction to SQL, we saw the FROM clause specify the teams table:

SELECT
 id, name
FROM
 teams

In Chapter 2, An Overview of the SELECT Statement, we saw the FROM clause specify the entries table:

SELECT
 title, category
FROM
 entries

This form of the FROM clause is as simple as it gets. There must be at least one tabular structure specified, and a single table fits that requirement. When we want to retrieve data from more than one table at the same time however, we need to start using joins.

FROM More than One Table Using JOINs

A join relates, associates, or combines two tables together. A join starts with two tables, then combines—or joins— them together in one of several different ways, producing a single tabular structure (as the result of the join). Actually, the verb to join is very descriptive of what happens, as we’ll see in a moment.

The way that the tables are joined—the type of join—is specified in the FROM clause using special keywords as well as the keyword JOIN. There are several different types of join, which I’ll describe briefly, so that you can see how they differ. Then we’ll look at specific join examples, using our sample applications.

Types of Join

A join combines the rows of two tables, based on a rule called a join condition; this compares values from the rows of both tables to determine which rows should be joined.

There are three basic types of join:

• inner join, created with the INNER JOIN keywords
• outer join, which comes in three varieties:
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN


• cross join, created with the CROSS JOIN keywords

To visualize how joins work, we’re going to use two tables named A and B, as shown below.

On Tables A and B

These tables are actually oversimplified, because they blur the distinction between table and column names. The join condition actually specifies the columns that must match. Further, it’s unusual for tables to have just one column.

Don’t worry about what A and B might actually represent. They could be anything. The idea in the following illustrations is for you to focus your attention on the values in the rows being joined. Table A has one column called a and rows with values 102, 104, 106, and 107. Table B has one column called b and rows with values 101, 102, 104, 106, and 108.

The Inner Join

For an inner join, only rows satisfying the condition in the ON clause are returned. Inner joins are the most common type of join. In most cases, such as the example below, the ON clause specifies that two columns must have matching values. In this case, if the value (of column a) in a row from one table (A) is equal to the value (of column b) in a row from the other table (B), the join condition is satisfied, and those rows are joined:

SELECT
 a, b
FROM
 A INNER JOIN B
   ON a=b

The figure below illustrates how this works.

As you can see, a row from A is joined to a row from B when their values are equal. Thus values 102, 104, and 106 are returned in the result set. Value 107 in A has no match in B, and therefore is not included in the result set. Similarly, the values 101 and 108 in B have no match in A, so they’re not included in the result set either. If it's easier to do so, you can think of it as though the matching rows are actually concatenated into a single longer row on which the rest of the SELECT statement then operates.