__ __ __ __ _____ _ _ _____ _ _ _ | \/ | \ \ / / | __ \ (_) | | / ____| | | | | | \ / |_ __\ V / | |__) | __ ___ ____ _| |_ ___ | (___ | |__ ___| | | | |\/| | '__|> < | ___/ '__| \ \ / / _` | __/ _ \ \___ \| '_ \ / _ \ | | | | | | |_ / . \ | | | | | |\ V / (_| | || __/ ____) | | | | __/ | | |_| |_|_(_)_/ \_\ |_| |_| |_| \_/ \__,_|\__\___| |_____/|_| |_|\___V 2.1 if you need WebShell for Seo everyday contact me on Telegram Telegram Address : @jackleetFor_More_Tools:
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)