Queries in SQL

Query specification

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:

• CROSS JOIN means that each record is joined with all records from the other table. CROSS JOIN is generally never used, since the result is usually too large.
• NATURAL means that two generalized tables must have a column of the same name. Those record couples that have the same value in this column are joined. A Join_specification may not be set in this case.
• join specification in the form ON condition means that only those couples of records for which the condition is fulfilled will be joined. Details about setting up the condition can be found here.
• join specification in the form USING ( column_name { , column_name }…) means that only those couple of records that have the same values in the specified columns will be joined. The specified columns must exist in both tables. This is a generalization of the NATURAL mode.
• If no join specification is set (or the NATURAL keyword used), each record will be joined with all records from the other table.
• LEFT [ OUTER ] JOIN means that each record from the first table must be joined with some record (at least one) from the second table. If no suitable record from the second table that fulfills the join conditions is found, then the record from the first table is joined with NULL values.
• RIGHT [ OUTER ] JOIN means that each record from the second table must be joined with some record (at least one) from the first table. If no suitable record from the first table that fulfills the join conditions is found, then the record from the second table is joined with NULL values.
• FULL [ OUTER ] JOIN means that each record from the table must be joined with some record (at least one) from the opposing table. If no suitable record from the opposing table that fulfills the join conditions is found, then the record is joined with NULL values.
• INNER JOIN or JOIN means that the records that fulfills the join_specification must be joined.

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)

• Use of a query_expression in brackets as a generalized table is a part of the Full Level.

Variations from the SQL Standard

• Index specification is an extension of 602SQL.

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

SELECT *
FROM Kb_topics 
   LEFT OUTER JOIN 
     (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
           ) 
         JOIN 
           (SELECT Kb_reports.topic_id,Count(*) AS un_answers 
            FROM Kb_reports JOIN Kb_answers ON (Kb_reports.id=Kb_answers.report_id) 
            WHERE (NOT EXISTS(SELECT * 
                              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

See

• Query collection

Queries in SQL

Query specification