FROM Clause: Select from What?

The FROM clause is mandatory for every SELECT statement — with the exception of the MS SQL Server case discussed earlier in this chapter. The database objects you should be able to select from are tables and views. These come in many flavors — temporary tables, inline views, materialized views, to name just a few — but the truth is that there is nothing else in the RDBMS world to select from.

Selecting from tables and views

We've used tables and views as "selectable from" objects in the examples given earlier in this chapter (and all previous chapters). In fact, the only truly selectable object in RDBMS is a table, while view is a query that is based on some table (or tables). Unlike a table, view, by definition, does not contain data but rather collects it from the base tables whenever a SELECT query is executed against the view.

Note 

Oracle also sports the concept of a materialized view. The regular view is based on some query that extracts data from the underlying tables; this query executes the very moment as the SELECT statement is executed against the view. To speed up the selection process, it is possible to create a materialized view that actually contains a snapshot of the data it's supposed to fetch. Such a view does not differ much from a table. The thing to remember here is that the data in a materialized view might not be up-to-date, and needs refreshing to ensure it.

Using aliases in a FROM clause

It is possible to alias the table names listed in the FROM clause of a SELECT query to shorten notation and make it more visual by prefixing the columns in a different clause with the table alias. Here is an example of selecting three columns from a STATUS table in the ACME database where the table is aliased with s:

SELECT status_id_n,         s.status_code_s,         s.status_desc_s  FROM   status s

The columns in the SELECT clause may or may not be prefixed with the table's alias (or table name); moreover, the columns themselves could be aliased for readability purposes, replacing somewhat cryptic column names with more meaningful ones (see paragraph earlier in this chapter). If such prefixes are used, they follow <object>.<property> notation, shown in the example above.

While not being very useful in the case of a single table selection, it simplifies queries when more than one table is involved, and helps to remove confusion should two or more tables have identically named columns. Please refer to Chap-ter 9 for more information on multitable queries.

Note 

Using alias for the tables in the FROM clause of the SELECT query is not the same as CREATE ALIAS statement, described in Chapter 4. The latter creates a database object that subsequently could be used to refer to the object (not necessarily a table) by some other, usually more convenient way. The alias in the SELECT query serves somewhat the same purpose — shortening the calling notation — but is radically different because it only refers to a table (or view) and exists only for the time the query is running, and disappears afterward.

There are certain rules on using the aliases in the other clauses that comprise the SELECT query. The table that was aliased in the FROM clause could be referred to by this alias throughout the whole query: SELECT, WHERE, GROUP BY, ORDER BY, and so on. Though it is possible to use column names in these clauses without qualifying them by the table name (or alias), it is recommended to use aliases (if specified) to prevent ambiguity.

Using subqueries in a FROM clause (inline views)

We have discussed the VIEW database object in Chapter 4 and Chapter 5. Here we are going to introduce so-called inline views. Unlike the VIEW object, the inline views do not exist outside the query that contains them, and may or may not have a proper name for themselves. Consider the following statement that selects customer's ID, name, and status from an inline view CUST:

SELECT  cust.id,         cust. cust_name_s,          cust.active  FROM    (SELECT cust_id_n AS id,                  cust_status_s AS active,                  cust_name_s,                 cust_alias_s AS alias,                  cust_credhold_s AS hold           FROM   customer) cust     ID          CUST_NAME_S                   ACTIVE ----------- ---------------------------   ------ 51 DLH INDUSTRIES                         Y 5 FAIR AND SONS AIR CONDTNG               Y 12 KILBURN GLASS INDUSTRIES               Y 61 BOSWELL DESIGNS CORP.                  Y 55 WILE ELECTROMATIC INC.                 Y 6 FABRITEK INC.                           Y      ... 16 DALCOMP INC.                           Y 89 INTEGRATED POWER DESIGNS               Y 85 GUARDIAN MANUFACTURING INC.            Y 152 WILE BESS COMPANY                     Y     37 record(s) selected.

Note that the outer SELECT clause refers to the columns selected from the inline view by their alias — because of the way these columns are exposed to it; replacing CUST.ID with CUST. CUST_ID_N would generate an Invalid column name error, since that is not the name that outer query could reference. At the same time, the column CUST_NAME_S could be used the way it is mentioned in the subquery because it was not aliased.