MacLochlainns Weblog

FROM Clause

Learning Outcomes

• Learn how to reference a pseudo table.
• Learn how to reference a table.
• Learn how to reference two tables.
• Learn how to reference three or more tables.

Lesson Materials

As explained in the SELECT statement page, the minimum SELECT statement requires a SELECT-list and FROM clause. All other clauses are optional clauses, like the WHERE, GROUP BY, HAVING, and ORDER BY clauses.

FROM Clause

The FROM clause qualifies the table sources for a query and any table aliases. The FROM clause supports two SQL specifications. They are ANSI SQL-89 and SQL-92.

SQL specification are defined by American National Standards Institute (ANSI). SQL1 was the first version of the specification formalized by ANSI in 1986. The formal name for SQL1 is SQL-86. SQL1 went through a minor revision but ANSI SQL-89 was accepted by Federal Information Processing Standards (FIPS). SQL2 was a major revision in 1992 and it added a natural join and several other features, including the CASE operator. The CASE operator lets developers perform conditional logic in queries and Data Manipulation Language (DML) commands, like the INSERT, UPDATE, MERGE, and DELETE statements.

SQL3 was a major release that included hierarchical and recursive queries, triggers, and Common Table Expressions (CTEs).

SQL3 was introduced in 1992. More often than not SQL1 and SQL2 are associated with comma-delimited table names in the FROM clause. SQL3 was a major revision of the SQL specification. SQL3 is associated with the use of table names with or without table aliases separated by join keywords, like the following:

• [CROSS] JOIN; the CROSS keyword is optional.
• FULL [OUTER] JOIN; the OUTER keyword is optional.
• INNER JOIN; the INNER keyword is optional.
• LEFT [OUTER] JOIN; the OUTER keyword is optional.
• NATURAL JOIN
• RIGHT [OUTER] JOIN; the OUTER keyword is optional.

The cross and natural joins do not use subclauses to qualify join relations. The cross join doesn’t use actually have a join relationship because it simply matches every row in one table with all the rows in the other table. The natural join on the other hand does use a join relationship, which is automatically determined by matching values of columns that share the same column names.

The inner, left, right, and full joins use subclasses to qualify join relations. These joins use either the ON or USING subclauses in the FROM clause. The ON subclause is generally available in most distributions of SQL but the USING subclause is not consistently available.

FROM Clause with a single table

The following query builds on the example in the GROUP BY clause by adding a HAVING clause. The HAVING clause filters only the aggregated row set. It returns only those rows that return an average cost per ounce greater than twenty-two cents.

1
2
3
4
5
6
7

COL brand FORMAT A30 HEADING "Brand"
COL avg_per_ounce     FORMAT A10  HEADING "Average|Cost|Ounces"
SELECT   brand
,        LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce
FROM     cereal
GROUP BY brand
HAVING   AVG(suggested_retail/ounces) > .22;

It returns the following nine rows from the thirty-six aggregated rows:

			       Average
			       Cost
Brand			       Ounces
------------------------------ ----------
Blueberry Toast Crunch		    $0.27
Apple Toast Crunch		    $0.27
Chocolate Cheerios		    $0.23
Strawberry Toast Crunch 	    $0.27
Lucky Charm			    $0.24
Cocoa Puffs			    $0.31
Cinnamon Vanilla Lucky Charm	    $0.24
Cocoa Pebbles			    $0.31
Trix				    $0.24

The HAVING clause is the last clause that effects the number of rows returned. After the HAVING clause, the only clause that follows it is the ORDER BY clause. The ORDER BY clause simply orders the rows already returned.