/usr/share/doc/firebird/sql.extensions
SQL Language Extension: derived tables Function: A derived table is a table derived from a select statement. Derived tables can also be nested to build complex queries. They can be joined the same as normal tables and views. Author: Arno Brinkman <firebird@abvisie.nl> Format: SELECT <select list> FROM <table reference list> <table reference list> ::= <table reference> [{<comma> <table reference>}...] <table reference> ::= <table primary> | <joined table> <table primary> ::= <table> [[AS] <correlation name>] | <derived table> | <lateral derived table> <derived table> ::= <query expression> [[AS] <correlation name>] [<left paren> <derived column list> <right paren>] <derived column list> ::= <column name> [{<comma> <column name>}...] <lateral derived table> ::= LATERAL <derived table> Notes: - Every column in the derived table must have a name. Unnamed expressions like constants should be added with an alias or the column list should be used. - The number of columns in the column list should be the same as the number of columns from the query expression. - The optimizer can handle a derived table very efficiently, but if the derived table contains a sub-select then no join order can be made (if the derived table is included in an inner join). - Keyword LATERAL allows the derived table to reference fields from the priorly listed tables in the current <table reference list>. Examples: a) Simple derived table: SELECT * FROM (SELECT RDB$RELATION_NAME, RDB$RELATION_ID FROM RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID) b) Aggregate on a derived table which also contains an aggregate: SELECT DT.FIELDS, Count(*) FROM (SELECT R.RDB$RELATION_NAME, Count(*) FROM RDB$RELATIONS R JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME) GROUP BY R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS) GROUP BY DT.FIELDS c) UNION and ORDER BY example: SELECT DT.* FROM (SELECT R.RDB$RELATION_NAME, R.RDB$RELATION_ID FROM RDB$RELATIONS R UNION ALL SELECT R.RDB$OWNER_NAME, R.RDB$RELATION_ID FROM RDB$RELATIONS R ORDER BY 2) AS DT WHERE DT.RDB$RELATION_ID <= 4 d) LATERAL derived table: SELECT * FROM (SELECT RDB$RELATION_NAME, RDB$RELATION_ID FROM RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID) CROSS JOIN LATERAL (SELECT COUNT(*) FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = R.RELATION_NAME) AS RF (FIELD_COUNT)
.
Edit
..
Edit
README.PSQL_stack_trace.txt
Edit
README.aggregate_filter.md
Edit
README.aggregate_tracking
Edit
README.alternate_string_quoting.txt
Edit
README.autonomous_transactions.txt
Edit
README.blob_append.md
Edit
README.builtin_functions.txt
Edit
README.case
Edit
README.coalesce
Edit
README.column_type_psql.txt
Edit
README.common_table_expressions
Edit
README.context_variables
Edit
README.context_variables2
Edit
README.cumulative_roles.txt
Edit
README.current_time
Edit
README.cursor_variables.txt
Edit
README.cursors
Edit
README.data_type_results_of_aggregations.txt
Edit
README.data_types
Edit
README.db_triggers.txt
Edit
README.ddl.txt
Edit
README.ddl_access.txt
Edit
README.ddl_triggers.txt
Edit
README.default_parameters
Edit
README.derived_tables.txt
Edit
README.distinct
Edit
README.domains_psql.txt
Edit
README.exception_handling
Edit
README.execute_block
Edit
README.execute_statement
Edit
README.execute_statement2
Edit
README.explicit_locks
Edit
README.expression_indices
Edit
README.external_connections_pool
Edit
README.floating_point_types.md
Edit
README.global_temporary_tables
Edit
README.hex_literals.txt
Edit
README.identity_columns.txt
Edit
README.iif
Edit
README.isc_info_xxx
Edit
README.joins.txt
Edit
README.keywords
Edit
README.leave_labels
Edit
README.length
Edit
README.linger
Edit
README.list
Edit
README.management_statements_psql.md
Edit
README.mapping.html
Edit
README.merge.txt
Edit
README.null_value
Edit
README.nullif
Edit
README.offset_fetch.txt
Edit
README.order_by_expressions_nulls
Edit
README.packages.txt
Edit
README.plan
Edit
README.regr_functions.txt
Edit
README.returning
Edit
README.rows
Edit
README.savepoints
Edit
README.scrollable_cursors.txt
Edit
README.select_expressions
Edit
README.sequence_generators
Edit
README.set_bind.md
Edit
README.set_role
Edit
README.set_transaction.txt
Edit
README.similar_to.txt
Edit
README.sql_security.txt
Edit
README.statistical_functions.txt
Edit
README.subroutines.txt
Edit
README.substring_similar.txt
Edit
README.time_zone.md
Edit
README.trim
Edit
README.universal_triggers
Edit
README.update_or_insert
Edit
README.user_management
Edit
README.view_updates
Edit
README.window_functions.md
Edit