Rules and Restrictions for the FROM Clause
The rules and restrictions are:
database_name.table_name
user_name.table_name
The FROM clause is … |
In the main query of these SQL statements … |
not required |
|
optional |
|
optional depending on the syntax used |
DELETE |
The following table lists the effects of column references on self‑join processing. Note that these rules apply only to cases where a single aliasing FROM clause is supplied.
IF this column reference … |
IS … |
THEN a self-join is … |
all |
unqualified (the preceding table name or correlation name is omitted) |
not performed. |
qualified (but the qualifiers reference only the correlation name) |
||
some, but not all |
qualified and the qualifiers reference only the correlation name |
|
qualified and the qualifiers reference only the permanent table name |
moot because no assumptions can be made about the owner of the unqualified columns. The self-join not performed. |
For a successful self‑join operation, column references must be fully qualified (that is, specified in the form tablename.columname) and the qualifiers should involve both the permanent and the correlation table names, as shown in the “Example 1: FROM Clause Used for a Self‑Join.”
Once a correlation name is declared, any subsequent reference to the base table name causes a new instance of the table to be used; the result of the SELECT can be a Cartesian self‑join.
In “Example 1: FROM Clause Used for a Self‑Join,” e, table_1 is given the correlation
name t. The subsequent specification of table_1 in the WHERE clause then causes the statement to perform a Cartesian product.
SELECT *
FROM table_1 AS t
WHERE table_1.column_1 = 2;