FROM clause and table references in DB2 SELECT

SELECT FROM

A subselect without FROM has nowhere to get rows. The classic z/OS pattern is:

1
2
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.EMPLOYEE;

FROM HR.EMPLOYEE means: the intermediate result starts as the rows of that table (subject to authorization, row permissions, archive/temporal settings, and later WHERE). SELECT then picks columns from those rows.

You can list more than one table-reference. Old-style comma FROM is a cross join unless WHERE relates the tables. Prefer explicit JOIN syntax on later pages; for this page, know that every FROM item is still a table-reference.

1
2
3
4
SELECT E.EMPNO, E.LASTNAME, D.DEPTNAME
  FROM HR.EMPLOYEE AS E,
       HR.DEPARTMENT AS D
 WHERE E.WORKDEPT = D.DEPTNO;

What a table-reference can be

The SQL Reference lists many forms. Beginners live in the first few. The rest exist so you recognize them in EXPLAIN and in other people’s SQL.

• single-table-reference — table-name, optional period-specification for temporal tables, optional correlation-clause
• single-view-reference — view-name with the same optional clauses
• joined-table — INNER / OUTER JOIN (later pages)
• nested-table-expression — TABLE (fullselect) with a correlation name
• table-function-reference — TABLE(function(...))
• xmltable-expression, collection-derived-table, data-change-table-reference (FINAL TABLE / OLD TABLE around INSERT UPDATE DELETE MERGE) — advanced

Each table-name or view-name in every FROM clause of the same statement must identify an object that exists at the same Db2 subsystem (three-part names reach another location). You cannot FROM a table that was implicitly created for an XML column. A view that includes GROUP BY or HAVING must not be used in a subquery of a basic predicate.

Table references

A base table is the usual source. Write a name Db2 can resolve, and hold the SELECT privilege (plus any row/column access control).

1
2
3
4
5
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE;
 
SELECT EMPNO, LASTNAME
  FROM EMPLOYEE;   -- unqualified: current schema must be HR (or a public alias/synonym)

If the table is archive-enabled, the SYSIBMADM.GET_ARCHIVE global variable and the ARCHIVESENSITIVE bind option decide whether archive rows appear. If it is a temporal table, a period-specification such as FOR SYSTEM_TIME AS OF can restrict rows to a point in time. Without a period specification, you typically see current rows, not history. Those are specialist topics; the beginner rule is: FROM table-name means “the table as it is currently exposed to this statement.”

View references

A view is a named SELECT stored in the catalog. FROM a view is legal and common. The intermediate result is the view’s result table. Column names are the view’s column names.

1
2
SELECT EMPNO, LASTNAME, WORKDEPT
  FROM HR.V_EMP_ACTIVE;

You do not automatically see every column of the underlying tables—only what the view projects. Some views are read-only; some are updatable. For SELECT, both work. If the view definition is a join or contains DISTINCT or GROUP BY, you are selecting from that computed result, which affects optimization and whether Db2 can push predicates.

Period specifications on a view apply to temporal tables accessed when the view is computed. If the view touches no temporal tables, the period clause has no effect.

Qualified table names

Unqualified names depend on CURRENT SCHEMA (dynamic SQL) or the bindQUALIFIER (static SQL), plus alias and public alias resolution. Two-part names remove that guesswork. Three-part names are how a local package talks to a table at another location through the Distributed Data Facility. The location name is not a schema.

1
2
3
4
5
-- Local two-part
SELECT LASTNAME FROM HR.EMPLOYEE;
 
-- Remote three-part (location name depends on your CDB / communications setup)
SELECT LASTNAME FROM DB2TEST.HR.EMPLOYEE;

Delimited identifiers let you use mixed case or reserved words: FROM HR."Employee Table". Prefer ordinary uppercase names in new designs. The identifiers page covers the exact rules.

Alias (catalog object)

CREATE ALIAS defines a stored alternate name for a table or view (and some other objects). Aliases live in the catalog (SYSIBM.SYSALIASES). Any SQL that is allowed to see the alias can use it in FROM as a table-reference.

1
2
3
4
CREATE ALIAS HR.EMP FOR HR.EMPLOYEE;
 
SELECT EMPNO, LASTNAME
  FROM HR.EMP;

Aliases are how shops give applications a stable name while the real table lives in another schema, or how they point a local name at a three-part remote table. DROP ALIAS removes the name, not the base table.

Synonyms (CREATE SYNONYM) are an older, unqualified alternate name tied to the creator. New work should use aliases. You will still FROM a synonym in legacy programs; resolution happens before the table is accessed, and the EXPLAIN object name may show the base table.

An alias-name that is not followed by a correlation name is an exposed name. You qualify columns as ALIAS.COL or, if you add AS E, as E.COL—not both.

Correlation names

A correlation name is a name you give a table-reference for the rest of that statement. It is valid only in the context where it is defined. Use it to shorten names, to qualify columns, to join a table to itself, or to write correlated subqueries.

1
2
3
SELECT E.EMPNO, E.LASTNAME, E.WORKDEPT
  FROM HR.EMPLOYEE AS E
 WHERE E.WORKDEPT = 'A00';

Leave one or more blanks between the table name and the correlation name. The word AS is optional and recommended for readability:

1
2
FROM HR.EMPLOYEE E
FROM HR.EMPLOYEE AS E

Once a correlation name is defined for that instance of the table, only the correlation name can qualify columns of that instance in that SELECT. FROM HR.EMPLOYEE AS E means you write E.EMPNO, not HR.EMPLOYEE.EMPNO, for that reference.

Exposed versus non-exposed names

Names in FROM are exposed or not:

• A correlation name is always exposed
• A table, view, alias, or synonym name is exposed only if you did not give it a correlation name

1
2
-- E is exposed; EMPLOYEE is not. DEPARTMENT is exposed.
FROM HR.EMPLOYEE AS E, HR.DEPARTMENT

Qualified column references must use an exposed name. Exposed names in a FROM clause should be unique. If you list the same table twice (self-join), at least one reference needs a correlation name—usually both do:

1
2
3
4
5
6
7
SELECT E.EMPNO, E.LASTNAME,
       M.EMPNO AS MGR_EMPNO, M.LASTNAME AS MGR_NAME
  FROM HR.EMPLOYEE AS E
  INNER JOIN HR.EMPLOYEE AS M
          ON E.WORKDEPT = M.WORKDEPT
         AND E.JOB = 'CLERK'
         AND M.JOB = 'MANAGER';

Nested table expressions and table functions require a correlation name so you can name the result:

1
2
3
4
5
6
7
8
SELECT X.EMPNO, X.NAME
  FROM TABLE (
         SELECT EMPNO,
                STRIP(FIRSTNME) CONCAT ' ' CONCAT STRIP(LASTNAME) AS NAME
           FROM HR.EMPLOYEE
          WHERE WORKDEPT = 'A00'
       ) AS X
 WHERE X.NAME LIKE 'A%';

You may also list column names in the correlation-clause to rename the result columns. Those listed names become the exposed column names; the underlying names are no longer exposed.

Correlated subqueries

A correlation name defined in an outer FROM can be referenced from a subquery as X.column. That is a correlated reference: the inner query depends on the current outer row.

1
2
3
4
5
6
SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL
  FROM HR.EMPLOYEE AS X
 WHERE EDLEVEL >
       (SELECT AVG(EDLEVEL)
          FROM HR.EMPLOYEE
         WHERE WORKDEPT = X.WORKDEPT);

The inner FROM HR.EMPLOYEE is a second instance of the table (no correlation name required there). X.WORKDEPT reaches the outer row. Correlation names can be reused in different statements or different clauses; they do not collide across statements.

SYSIBM.SYSDUMMY1 as a table-reference

When syntax needs FROM but you do not care about stored rows, use the one-row catalog table SYSIBM.SYSDUMMY1:

1
2
SELECT CURRENT DATE, 2 + 3 * 4
  FROM SYSIBM.SYSDUMMY1;

Later lessons cover VALUES as a source and SELECT without FROM. On z/OS, SYSDUMMY1 remains the portable, interviewer-friendly idiom.

Authorization, implicit qualification, and missing objects

FROM is also a security checkpoint. The statement authorization ID must hold the SELECT privilege on each table or view (or a higher privilege that includes it), unless a view or stored procedure already encapsulates access. Row permissions and column masks, when enabled, further restrict which rows and values appear. A failed FROM is often SQLCODE -204 (object not found—sometimes because the schema is wrong) or -551 / -552 (not authorized). Beginners should not guess: DISPLAY the current schema, qualify the name, and check SYSIBM.SYSTABLES.

Implicit qualification is a common -204 source. Dynamic SQL uses CURRENT SCHEMA. Static SQL uses the plan or package QUALIFIER unless the name is already two-part. A program that works in SPUFI under your TSO ID can fail in batch under a different USER/SQLID. Two-part names in FROM are the simplest defense.

If two table-references produce a cartesian product—FROM A, B with no join condition— every row of A pairs with every row of B. Fifty thousand employees times two hundred departments is ten million rows before WHERE. That is rarely what you meant. Write an ON clause (next JOIN pages) or a WHERE equality that relates keys. DISTINCT after a cartesian product is not a design.

Optional FROM forms you will see later

Nested table expressions (a fullselect in parentheses in FROM) let you filter on an AS name that WHERE could not see in the inner select list. Table functions return rows from a function instead of a stored table. FINAL TABLE (INSERT …) lets you SELECT the rows you just inserted in one statement. XMLTABLE turns XML into relational rows. Collection derived tables unnest arrays. None of these change the beginner mental model: FROM still names a result table, and a correlation name is how you call that result for the rest of the query.

Comma-separated FROM lists and explicit JOIN both produce a joined table. Prefer JOIN … ON because the join predicate sits next to the tables it relates. Mixing old commas with OUTER JOIN has extra precedence rules that confuse everyone. Pick explicit JOIN in new SQL.

Correlation names also appear on UPDATE and DELETE targets: UPDATE HR.EMPLOYEE AS E SET … WHERE E.SALARY > 100000. The same exposed-name rules apply. Learning them on SELECT FROM pays off in every other statement that names a table.

Explain It Like I'm Five

FROM is the sentence “take toys out of this box.” The box can be a real toy chest (table), a window that already shows some toys (view), a sticker on a chest that points to another chest (CREATE ALIAS), or a nickname you use only while you talk (“call that chest E”) so when two chests look alike you still know which toy you mean. A long address on the box—school.classroom.chest—is a qualified name so nobody opens the wrong chest in the wrong building.

Exercises

1. Write a two-part FROM for a table in schema HR and select two columns.
2. Add AS E and qualify both columns with E.
3. Explain whether CREATE ALIAS HR.EMP FOR HR.EMPLOYEE still exists after the query ends, versus FROM HR.EMPLOYEE AS E.
4. In FROM HR.EMPLOYEE AS E, HR.DEPARTMENT, list the exposed names.
5. Write a query that uses SYSIBM.SYSDUMMY1 to return CURRENT TIMESTAMP.