602SQL Documentation Index  

Generalized Table (SQL)

generalized_table ::= { table_identifier [ index_specification ] 
    | stored_query_identifier | system_query | ( query_expression ) | join }
    [ rename ]
index_specification ::= INDEX index_name
join ::= generalized_table join_type generalized_table [ join_specification ]
join_type ::= CROSS JOIN | [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ] JOIN 
join_specification ::= ON condition | USING ( column_name { , column_name } )
rename ::= [ AS ] name [( column_name { , column_name } )]

The generalized_table expression defines, which objects may serve as tables in the SQL language. Besides the actual table or a query stored in the database (VIEW in SQL terminology) and system queries, a query_expression in parenthesis (subquery) or a join can be considered a generalized table.

The join expression represents the creation of a conjunction of records from two tables by chaining the column values of one record from the first table and one record from the second table. Join_type and join_specification set the method of joining records from the tables:

Index_specification on the table name forces the use of the specified index regardless of the choice from the optimizer.

If you specify rename at the end of the generalized table, you may assign a name that can be used to refer to this table. You may also specify column names of this table. The number of column names must be the same as the number of columns in the generalized table.

Variations from the Intermediate to the Full Level (SQL 3)

Variations from the SQL Standard

Example: The use of query expressions and joins as generalized tables:

FROM Kb_topics 
     (SELECT topic_id,un_reports+un_answers AS unanswered 
      FROM (SELECT topic_id,Count(*) AS un_reports 
            FROM Kb_reports WHERE all_answer_count=0 GROUP BY topic_id
           (SELECT Kb_reports.topic_id,Count(*) AS un_answers 
            FROM Kb_reports JOIN Kb_answers ON (Kb_reports.id=Kb_answers.report_id) 
                              FROM Kb_answers answ 
                              WHERE Kb_answers.id=answ.question_id)) 
            GROUP BY Kb_reports.topic_id
         USING (topic_id)) 
      ON (Kb_topics.id=topic_id) 
ORDER BY Kb_topics.pos