FROM Clause
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
; theCROSS
keyword is optional.FULL [OUTER] JOIN
; theOUTER
keyword is optional.INNER JOIN
; theINNER
keyword is optional.LEFT [OUTER] JOIN
; theOUTER
keyword is optional.NATURAL JOIN
RIGHT [OUTER] JOIN
; theOUTER
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 | COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal; |
It returns the following row from the seventy rows because the AVG
function aggregates, or calculates the average of, all of the original seventy rows into one row:
Average Cost Ounces ---------- $0.21 |
You can only return aggregated functions without a GROUP BY
clause, which you’ll see next.