TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
Data Manipulation Language (DML)
Table of Contents
Quick Links
Articles
In this chapter are the additions and improvements that h...
Important
A new configuration parameter, named RelaxedAliasChecking...
This parameter will not be a permanent fixture in Firebir...
Quick Links
Common Table Expressions
The LIST Function
The RETURNING Clause
UPDATE OR INSERT Statement
MERGE Statement
New JOIN Types
INSERT with Defaults
BLOB Subtype 1 Compatibility with VARCHAR
Full Equality Comparisons Between BLOBs
RDB$DB_KEY Returns NULL in Outer Joins
Sorting on BLOB and ARRAY Columns is Restored
Built-in Functions
Functions Enhanced in V.2.0.x
DSQL Parsing of Table Names is Stricter
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
Enhancements to UNION Handling
Enhancements to NULL Logic
Subqueries and INSERT Statements Can Now Accept UNION Sets
New Extensions to UPDATE and DELETE Syntaxes
Extended Context Variables
Improvements in Handling User-specified Query Plans
Improvements in Sorting
NEXT VALUE FOR Expression
Common Table Expressions
LIST Function
RETURNING Clause
UPDATE OR INSERT Statement
MERGE Statement
New JOIN Types
NAMED COLUMNS & NATURAL JOIN
CROSS JOIN
INSERT with Defaults
Text BLOB Compatibility
Compare BLOB=BLOB
Sorting on BLOBs
RDB$DB_KEY Returns NULL in Outer Joins
New Built-in Functions
Enhancements to Built-in Functions
IIF() Expression
Improvement in CAST() Behaviour
CAST(x as <domain-name>)
Expression Arguments for SUBSTRING()
DSQL Parsing of Table Names is Stricter
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
UNION DISTINCT
Improved Type Coercion in UNIONs
UNIONs Allowed in ANY/ALL/IN Subqueries
New [NOT] DISTINCT Predicate
NULL Comparison Rule Relaxed
NULLs Ordering Changed
UNION Sets in Subquery Constructs
Extended Context Variables
Query Plans Improvements
GROUP or ORDER by Alias Name
GROUP BY Arbitrary Expressions
Order * Sets by Implicit Degree Number
NEXT VALUE FOR
Articles
Select Statement & Expression Syntax
Data Type of an Aggregation Result
A Useful Trick with Date Literals
Common Table Expressions
Vlad Khorsun
Based on work by Paul Ruizendaal for Fyracle project
(v.2.1) A common table expression (CTE) is like a view th...
Benefits of CTEs
Using CTEs allows you to specify dynamic queries that are...
The engine begins execution from a non-recursive member.
For each row evaluated, it starts executing each recursiv...
If the currently executing instance of a recursive member...
The memory and CPU overhead of a recursive CTE is much le...
Recursion Limit
Currently the recursion depth is limited to a hard-coded ...
Syntax and Rules for CTEs
select :
select_expr for_update_clause lock_clause
select_expr :
with_clause select_expr_body order_clause rows_clause
| select_expr_body order_clause rows_clause
with_clause :
WITH RECURSIVE with_list | WITH with_list
with_list :
with_item | with_item ',' with_list
with_item :
symbol_table_alias_name derived_column_list
AS '(' select_expr ')'
select_expr_body :
query_term
| select_expr_body UNION distinct_noise query_term
| select_expr_body UNION ALL query_term
A less formal representation:
WITH [RECURSIVE]
CTE_A [(a1, a2, …)]
AS ( SELECT … ),
CTE_B [(b1, b2, …)]
AS ( SELECT … ),
...
SELECT ...
FROM CTE_A, CTE_B, TAB1, TAB2 ...
WHERE ...
Rules for Non-Recursive CTEs
Multiple table expressions can be defined in one query
Any clause legal in a SELECT specification is legal in ta...
Table expressions can reference one another
References between expressions should not have loops
Table expressions can be used within any part of the main...
The same table expression can be used more than once in t...
Table expressions (as subqueries) can be used in INSERT, ...
Table expressions are legal in PSQL code
WITH statements can not be nested
Example of a non-recursive CTE
WITH
DEPT_YEAR_BUDGET AS (
SELECT FISCAL_YEAR, DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
)
SELECT D.DEPT_NO, D.DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPARTMENT D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996
WHERE EXISTS (
SELECT * FROM PROJ_DEPT_BUDGET B
WHERE D.DEPT_NO = B.DEPT_NO)
Rules for Recursive CTEs
A recursive CTE is self-referencing (has a reference to i...
A recursive CTE is a UNION of recursive and non-recursive...
At least one non-recursive member (anchor) must be present
Non-recursive members are placed first in the UNION
Recursive members are separated from anchor members and f...
non-recursive member (anchor)
UNION [ALL | DISTINCT]
non-recursive member (anchor)
UNION [ALL | DISTINCT]
non-recursive member (anchor)
UNION ALL
recursive member
UNION ALL
recursive member
References between CTEs should not have loops
Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate fun...
A recursive member can have only one reference to itself ...
A recursive reference cannot participate in an outer join
Example of a recursive CTE
WITH RECURSIVE
DEPT_YEAR_BUDGET AS
(
SELECT FISCAL_YEAR, DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS
(
SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT,
CAST('' AS VARCHAR(255)) AS INDENT
FROM DEPARTMENT
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT,
H.INDENT || ' '
FROM DEPARTMENT D
JOIN DEPT_TREE H
ON D.HEAD_DEPT = H.DEPT_NO
)
SELECT D.DEPT_NO,
D.INDENT || D.DEPARTMENT AS DEPARTMENT,
B_1993.BUDGET AS B_1993,
B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995,
B_1996.BUDGET AS B_1996
FROM DEPT_TREE D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996
The LIST Function
Oleg Loa
Dmitry Yemanov
(v.2.1) This function returns a string result with the co...
Format
<list function> ::=
LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <...
] ')'
<delimiter value> ::=
{ <string literal> | <parameter> | <variable> }
Syntax Rules
If neither ALL nor DISTINCT is specified, ALL is implied.
If <delimiter value> is omitted, a comma is used to separ...
Other Notes
Numeric and date/time values are implicitly converted to ...
The result value is of type BLOB with SUB_TYPE TEXT for a...
Ordering of values within a group is implementation-defined.
Examples
/* A */
SELECT LIST(ID, ':')
FROM MY_TABLE
/* B */
SELECT TAG_TYPE, LIST(TAG_VALUE)
FROM TAGS
GROUP BY TAG_TYPE
The RETURNING Clause
Dmitry Yemanov
Adriano dos Santos Fernandes
(v.2.1) The purpose of this SQL enhancement is to enable ...
The most likely usage is for retrieving the value generat...
In DSQL, the execution of the operation itself and the re...
Because the RETURNING clause is designed to return a sing...
Note
In DSQL, the statement always returns the set, even if th...
In PSQL, if no row was affected by the statement, nothing...
Syntax Patterns
INSERT INTO ... VALUES (...)
[RETURNING <column_list> [INTO <variable_list>]]
INSERT INTO ... SELECT ...
[RETURNING <column_list> [INTO <variable_list>]]
UPDATE OR INSERT INTO ... VALUES (...) ...
[RETURNING <column_list> [INTO <variable_list>]]
UPDATE ... [RETURNING <column_list> [INTO <variable_list>]]
DELETE FROM ...
[RETURNING <column_list> [INTO <variable_list>]]
Rules for Using a RETURNING Clause
The INTO part (i.e. the variable list) is allowed in PSQL...
The presence of the RETURNING clause causes an INSERT sta...
The RETURNING clause ignores any explicit record change (...
OLD and NEW context variables can be used in the RETURNIN...
In UPDATE and INSERT OR UPDATE statements, field referenc...
Examples
INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 INTO :V1, :V2;
INSERT INTO T2 (F1, F2)
VALUES (1, 2)
RETURNING ID INTO :PK;
DELETE FROM T1
WHERE F1 = 1
RETURNING F2;
UPDATE T1
SET F2 = F2 * 10
RETURNING OLD.F2, NEW.F2;
UPDATE OR INSERT Statement
Adriano dos Santos Fernandes
(v.2.1) This syntax has been introduced to enable a recor...
Syntax Pattern
UPDATE OR INSERT INTO <table or view> [(<column_list>)]
VALUES (<value_list>)
[MATCHING (<column_list>)]
[RETURNING <column_list> [INTO <variable_list>]]
Examples
UPDATE OR INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2);
UPDATE OR INSERT INTO EMPLOYEE (ID, NAME)
VALUES (:ID, :NAME)
RETURNING ID;
UPDATE OR INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
MATCHING (F1);
UPDATE OR INSERT INTO EMPLOYEE (ID, NAME)
VALUES (:ID, :NAME)
RETURNING OLD.NAME;
Usage Notes
When MATCHING is omitted, the existence of a primary key ...
INSERT and UPDATE permissions are needed on <table or view>.
If the RETURNING clause is present, then the statement is...
Note
A “multiple rows in singleton select” error will be raise...
MERGE Statement
Adriano dos Santos Fernandes
(v.2.1) This syntax has been introduced to enable a recor...
Syntax Pattern
<merge statement> ::=
MERGE
INTO <table or view> [ [AS] <correlation name> ]
USING <table or view or derived table> [ [AS] <correlat...
ON <condition>
[ <merge when matched> ]
[ <merge when not matched> ]
<merge when matched> ::=
WHEN MATCHED THEN
UPDATE SET <assignment list>
<merge when not matched> ::=
WHEN NOT MATCHED THEN
INSERT [ <left paren> <column list> <right paren> ]
VALUES <left paren> <value list> <right paren>
Rules for MERGE
At least one of <merge when matched> and <merge when not ...
Neither should be specified more than once.
Note
A right join is made between the INTO and USING tables us...
If no record is returned from the join, INSERT is not cal...
Example
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET
name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (cd.id, cd.name)
New JOIN Types
Adriano dos Santos Fernandes
(v.2.1) Two new JOIN types are introduced: the NAMED COLU...
Syntax and Rules
<named columns join> ::=
<table reference> <join type> JOIN <table reference>
USING ( <column list> )
<natural join> ::=
<table reference> NATURAL <join type> JOIN <table primary>
Named columns join
All columns specified in <column list> should exist in th...
An equi-join (<left table>.<column> = <right table>.<colu...
The USING columns can be accessed without qualifiers—in t...
In “SELECT *”, USING columns are expanded once, using the...
Natural join
A “named columns join” is automatically created with all ...
If there is no common column, a CROSS JOIN is created.
Examples
/* 1 */
select * from employee
join department
using (dept_no);
/* 2 */
select * from employee_project
natural join employee
natural join project;
CROSS JOIN
D. Yemanov
(V.2.0.x) CROSS JOIN is now supported. Logically, this sy...
A CROSS JOIN B
is equivalent to either of the following:
A INNER JOIN B ON 1 = 1
or, simply:
FROM A, B
Performance Improvement at V.2.1.2
D. Yemanov
In the rare case where a cross join of three or more tabl...
INSERT with Defaults
D. Yemanov
Feature request
(v.2.1) It is now possible to INSERT without supplying va...
Example
INSERT INTO <table>
DEFAULT VALUES
[RETURNING <values>]
BLOB Subtype 1 Compatibility with VARCHAR
A. dos Santos Fernandes
(v.2.1) At various levels of evaluation, the engine now t...
Assignments, conversions and concatenations (|| operator)
Operators = , <>, >, <, >=, <=, BETWEEN, IS [NOT] DISTINC...
Functions CAST, BIT_LENGTH, CHAR[ACTER]_LENGTH, OCTET_LEN...
Note Carefully!
SUBSTRING(), when applied to a text BLOB, now returns a t...
If the FOR argument is absent, the BLOB returned will be ...
Existential predicators IN, ANY/SOME, ALL
Search predicators CONTAINING, STARTING [WITH], LIKE
Important
The predicating expression must not resolve to more than ...
A LIST expression. Note that, prior to v.2.1.4, the last ...
Full Equality Comparisons Between BLOBs
(v.2.0.x) Comparison can be performed on the entire conte...
RDB$DB_KEY Returns NULL in Outer Joins
A. dos Santos Fernandes
Feature request CORE-979
(v.2.1) By some anomaly, the physical RDB$DB_KEY has alwa...
Sorting on BLOB and ARRAY Columns is Restored
Dmitry Yemanov
(v.2.1) In earlier pre-release versions of Firebird 2.1, ...
That change was reversed in the RC2 pre-release version, ...
Important
This reversion to “bad old behaviour” does not in any way...
Built-in Functions
(v.2.1) Some existing built-in functions have been enhanc...
New Built-in Functions
Adriano dos Santos Fernandes
Oleg Loa
Alexey Karyakin
A number of built-in functions has been implemented in V....
Note
The choice between UDF and built-in function is decided w...
The new built-in function DECODE() does not have an equiv...
The functions are detailed in Appendix A.
Note
Several of these built-in functions were already availabl...
Enhancements to Functions
A. dos Santos Fernandes
EXTRACT(WEEK FROM DATE)
Feature request CORE-663
The EXTRACT() function is extended to support the ISO-860...
EXTRACT (WEEK FROM date '30.09.2007')
returns 39
alter table xyz
add WeekOfTheYear
computed by (
case
when (extract(month from CertainDate) = 12)
and (extract(week from CertainDate) = 1)
then
'Week '||extract (WEEK from CertainDate)||' of year '
|| (1 + (extract( year from CertainDate)))
else 'Week '||extract (WEEK from CertainDate)||' of y...
||extract( year from CertainDate)
end )
Specify the Scale for TRUNC()
Feature request CORE-1340
In Beta 1 the implementation of the TRUNC() function supp...
select
trunc(987.65, 1),
trunc(987.65, -1)
from rdb$database;
returns 987.60, 980.00
For other examples of using TRUNC() with and without the ...
Milliseconds Handling for EXTRACT(), DATEADD() and DATEDI...
Feature request CORE-1387
From v.2.1 Beta 2, EXTRACT(), DATEADD() and DATEDIFF() ca...
EXTRACT ( MILLISECOND FROM timestamp '01.01.2000 01:00:00...
returns 123
DATEADD ( MILLISECOND, 100, timestamp '01.01.2000 01:00:0...
DATEDIFF ( MILLISECOND, timestamp '01.01.2000 02:00:00.00...
For more explanatory examples of using DATEADD() and DATE...
Functions Enhanced in V.2.0.x
Some function enhancements were already available in the ...
IIF() Expression
O. Loa
(V.2.0.x) An IIF() expression can be used as a shortcut f...
IIF (<search_condition>, <value1>, <value2>)
is implemented as a shortcut for
CASE
WHEN <search_condition> THEN <value1>
ELSE <value2>
END
Example
SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
Improvement in CAST() Behaviour
D. Yemanov
(V.2.0.x) The infamous “Datatype unknown” error (SF Bug #...
Example
SELECT CAST(? AS INT) FROM RDB$DATABASE
CAST(x as <domain-name>)
A. dos Santos Fernandes
(V.2.1.x) Casting of compatible values or expressions can...
Syntax Pattern
CAST (<value> | <expression> AS <builtin-data-type> | <do...
Examples
CREATE DOMAIN DOM AS INTEGER;
...
SELECT CAST (10.44 AS TYPE OF DOM) AN_INTEGER
FROM RDB$DATABASE;
AN_INTEGER
----------
10
...
SELECT CAST (3.142/2 AS DOM) AN_INTEGER
FROM RDB$DATABASE;
AN_INTEGER
----------
2
Note
Directly casting to <domain-name> applies any default or ...
Expression Arguments for SUBSTRING()
O. Loa, D. Yemanov
(V.2.0.x) The built-in function SUBSTRING() can now take ...
Formerly, the inbuilt SUBSTRING() function accepted only ...
Tip
If your attempts to use this feature fail with “invalid t...
Changes to Results Returned from SUBSTRING()
(V.2.1.x) To conform with standards, the character length...
In Firebird 2.0 and 1.5, the returned value was a CHAR wi...
It is not necessary to redefine any PSQL variables you ha...
GOTCHA for BLOBs
Clearly, a text BLOB, being of indeterminate character le...
This change can break existing PSQL and expression code.
Watch out for overflows! Take particular care with CASTs ...
In v.2.1.x sub-releases prior to v.2.1.4, pay attention t...
DSQL Parsing of Table Names is Stricter
A. Brinkman
Alias handling and ambiguous field detecting have been im...
When a table alias is provided for a table, either that a...
Ambiguity checking now checks first for ambiguity at the ...
Examples
When an alias is present it must be used; or no alias at ...
This query was allowed in FB1.5 and earlier versions:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
but will now correctly report an error that the field "RD...
Use this (preferred):
SELECT
R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
or this statement:
SELECT
RDB$RELATION_NAME
FROM
RDB$RELATIONS R
The statement below will now correctly use the FieldID fr...
UPDATE
TableA
SET
FieldA = (SELECT SUM(A.FieldB) FROM TableA A
WHERE A.FieldID = TableA.FieldID)
Note
In Firebird it is possible to provide an alias in an upda...
This example did not run correctly in Firebird 1.5 and ea...
SELECT
RDB$RELATIONS.RDB$RELATION_NAME,
R2.RDB$RELATION_NAME
FROM
RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATIO...
If RDB$RELATIONS contained 90 records, it would return 90...
This would except with a syntax error in Firebird 1.5, bu...
SELECT
(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
FROM
RDB$RELATIONS
Ambiguity checking in subqueries: the query below would r...
SELECT
(SELECT
FIRST 1 RDB$RELATION_NAME
FROM
RDB$RELATIONS R1
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = R1.RDB$RELATION_NAME))
FROM
RDB$DATABASE
EXECUTE BLOCK Statement
V. Khorsun
The SQL language extension EXECUTE BLOCK makes "dynamic P...
Syntax pattern
EXECUTE BLOCK [ (param datatype = ?, param datatype = ...
[ RETURNS (param datatype, param datatype, ...) ]
AS
[DECLARE VARIABLE var datatype; ...]
BEGIN
...
END
For the client, the call isc_dsql_sql_info with the param...
isc_info_sql_stmt_select if the block has output paramete...
isc_info_sql_stmt_exec_procedure if the block has no outp...
The client should preprocess only the head of the SQL sta...
Example
The user SQL is
EXECUTE BLOCK (X INTEGER = :X)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
The preprocessed SQL is
EXECUTE BLOCK (X INTEGER = ?)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
Derived Tables
A. Brinkman
Implemented support for derived tables in DSQL (subquerie...
Syntax Pattern
SELECT
<select list>
FROM
<table reference list>
<table reference list> ::= <table reference> [{<comma...
<table reference> ::=
<table primary>
| <joined table>
<table primary> ::=
<table> [[AS] <correlation name>]
| <derived table>
<derived table> ::=
<query expression> [[AS] <correlation name>]
[<left paren> <derived column list> <right paren>]
<derived column list> ::= <column name> [{<comma> <co...
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 ag...
SELECT
DT.FIELDS,
Count(*)
FROM
(SELECT
R.RDB$RELATION_NAME,
Count(*)
FROM
RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NA...
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
Points to Note
Every column in the derived table must have a name. Unnam...
The number of columns in the column list should be the sa...
The optimizer can handle a derived table very efficiently...
ROLLBACK RETAIN Syntax
D. Yemanov
The ROLLBACK RETAIN statement is now supported in DSQL.
A “rollback retaining” feature was introduced in InterBas...
Firebird 2.0 adds an optional RETAIN clause to the DSQL R...
Syntax pattern: follows that of COMMIT RETAIN.
ROWS Syntax
D. Yemanov
ROWS syntax is used to limit the number of rows retrieved...
It is available in both DSQL and PSQL.
Syntax Pattern
SELECT ...
[ORDER BY <expr_list>]
ROWS <expr1> [TO <expr2>]
Examples
1.
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
ORDER BY COL
ROWS 10 TO 100
2.
SELECT COL1, COL2,
( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
FROM T4
3.
DELETE FROM T5
ORDER BY COL5
ROWS 1
Points to Note
When <expr2> is omitted, then ROWS <expr1> is semanticall...
There is nothing that is semantically equivalent to a SKI...
Enhancements to UNION Handling
The rules for UNION queries have been improved as follows:
UNION DISTINCT Keyword Implementation
D. Yemanov
UNION DISTINCT is now allowed as a synonym for simple UNI...
Syntax Pattern
UNION [{DISTINCT | ALL}]
Improved Type Coercion in UNIONs
A. Brinkman
Automatic resolution of the data type of the result of an...
Syntax Rules
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
Case:
If any of the data types in DTS is character string, then:
If any of the data types in DTS is variable-length charac...
Otherwise, the result data type is fixed-length character...
The characterset/collation is used from the first charact...
If all of the data types in DTS are exact numeric, then t...
Note
NOTE :: Checking for precision overflows is done at run-t...
If any data type in DTS is approximate numeric, then each...
If some data type in DTS is a date/time data type, then e...
If any data type in DTS is BLOB, then each data type in D...
UNIONs Allowed in ANY/ALL/IN Subqueries
D. Yemanov
The subquery element of an ANY, ALL or IN search may now ...
Enhancements to NULL Logic
The following features involving NULL in DSQL have been i...
New [NOT] DISTINCT Test Treats Two NULL Operands as Equal
O. Loa, D. Yemanov
A new equivalence predicate behaves exactly like the equa...
Thus, IS NOT DISTINCT treats (NULL equals NULL) as if it ...
Syntax Pattern
<value> IS [NOT] DISTINCT FROM <value>
Examples
1.
SELECT * FROM T1
JOIN T2
ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
2.
SELECT * FROM T
WHERE T.MARK IS DISTINCT FROM 'test';
Points to note
Because the DISTINCT predicate considers that two NULL va...
Read More About NULL
For more understanding of the way NULL comparisons are ev...
The NOT DISTINCT predicate can be optimized using an inde...
NULL Comparison Rule Relaxed
D. Yemanov
A NULL literal can now be treated as a value in all expre...
A = NULL
B > NULL
A + NULL
B || NULL
Note
All such expressions evaluate to NULL. The change does no...
NULLs Ordering Changed to Comply with Standard
N. Samofatov
Placement of nulls in an ordered set has been changed to ...
Important
If you override the default nulls placement, no index can...
Examples
Database: proc.fdb
SQL> create table gnull(a int);
SQL> insert into gnull values(null);
SQL> insert into gnull values(1);
SQL> select a from gnull order by a;
A
============
<null>
1
SQL> select a from gnull order by a asc;
A
============
<null>
1
SQL> select a from gnull order by a desc;
A
============
1
<null>
SQL> select a from gnull order by a asc nulls first;
A
============
<null>
1
SQL> select a from gnull order by a asc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls first;
A
============
<null>
1
Subqueries and INSERT Statements Can Now Accept UNION Sets
D. Yemanov
SELECT specifications used in subqueries and in INSERT IN...
New Extensions to UPDATE and DELETE Syntaxes
O. Loa
ROWS specifications and PLAN and ORDER BY clauses can now...
Users can now specify explicit plans for UPDATE/DELETE st...
Syntax Pattern
UPDATE ... SET ... WHERE ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
or
DELETE ... FROM ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
Extended Context Variables
A number of new facilities have been added to extend the ...
Sub-second Values Enabled for Time and DateTime Variables
D. Yemanov
CURRENT_TIMESTAMP, 'NOW' Now Return Milliseconds
The context variable CURRENT_TIMESTAMP and the date/time ...
Seconds Precision Enabled for CURRENT_TIME and CURRENT_TI...
CURRENT_TIME and CURRENT_TIMESTAMP now optionally allow s...
The feature is available in both DSQL and PSQL.
Syntax Pattern
CURRENT_TIME [(<seconds precision>)]
CURRENT_TIMESTAMP [(<seconds precision>)]
Examples
1. SELECT CURRENT_TIME FROM RDB$DATABASE;
2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
Note
The maximum possible precision is 3 which means accuracy ...
If no seconds precision is specified, the following value...
0 for CURRENT_TIME
3 for CURRENT_TIMESTAMP
New System Functions to Retrieve Context Variables
N. Samofatov
Values of context variables can now be obtained using the...
Syntax Pattern
RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
RDB$GET_CONTEXT( <namespace>, <variable> )
These functions are really a form of external function th...
Declaration
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
VARCHAR(80),
VARCHAR(80)
RETURNS VARCHAR(255) FREE_IT;
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
VARCHAR(80),
VARCHAR(80),
VARCHAR(255)
RETURNS INTEGER BY VALUE;
Usage
RDB$SET_CONTEXT and RDB$GET_CONTEXT set and retrieve the ...
Note
Namespace and variable names are case-sensitive.
RDB$GET_CONTEXT retrieves current value of a variable. If...
RDB$SET_CONTEXT sets a value for specific variable, if it...
To delete a variable from a context, set its value to NULL.
Pre-defined Namespaces
A fixed number of pre-defined namespaces is available:
USER_SESSION
Offers access to session-specific user-defined variables....
USER_TRANSACTION
Offers similar possibilities for individual transactions.
SYSTEM
Provides read-only access to the following variables:
NETWORK_PROTOCOL :: The network protocol used by client t...
CLIENT_ADDRESS :: The wire protocol address of the remote...
DB_NAME :: Canonical name of the current database. It is ...
ISOLATION_LEVEL :: The isolation level of the current tra...
TRANSACTION_ID :: The numeric ID of the current transacti...
SESSION_ID :: The numeric ID of the current session. The ...
CURRENT_USER :: The current user. The returned value is t...
CURRENT_ROLE :: Current role for the connection. Returns ...
Notes
To avoid DoS attacks against the Firebird Server, the num...
Example of Use
set term ^;
create procedure set_context(User_ID varchar(40), Trn_...
begin
RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_...
end ^
create table journal (
jrn_id integer not null primary key,
jrn_lastuser varchar(40),
jrn_lastaddr varchar(255),
jrn_lasttransaction integer
)^
CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UP...
as
begin
new.jrn_lastuser = rdb$get_context('USER_TRANSACTION'...
new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_...
new.jrn_lasttransaction = rdb$get_context('USER_TRANS...
end ^
commit ^
execute procedure set_context('skidder', 1) ^
insert into journal(jrn_id) values(0) ^
set term ;^
Since rdb$set_context returns 1 or zero, it can be made t...
Example
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0 means not defined already; we have set it to 'ru'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 means it was defined already; we have changed it to 'ca'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 says it existed before; we have changed it to NULL, i.e...
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0, since nothing actually happened this time: it was alre...
Improvements in Handling User-specified Query Plans
D. Yemanov
Plan fragments are propagated to nested levels of joins, ...
A user-supplied plan will be checked for correctness in o...
Short-circuit optimization for user-supplied plans has be...
A user-specified access path can be supplied for any SELE...
Syntax rules
The following schema describing the syntax rules should b...
PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_...
<stream_retrieval> ::= { <natural_scan> | <indexed_retrie...
<navigational_scan> }
<natural_scan> ::= <stream_alias> NATURAL
<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name>
[, <index_name> ...] )
<navigational_scan> ::= <stream_alias> ORDER <index_name>
[ INDEX ( <index_name> [, <index_name> ...] ) ]
<sorted_streams> ::= SORT ( <stream_retrieval> )
<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_r...
[, <stream_retrieval> ...] )
| [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
Details
Natural scan means that all rows are fetched in their nat...
Indexed retrieval uses an index range scan to find row id...
Navigational scan uses an index to return rows in the giv...
The index b-tree is walked from the leftmost node to the ...
If any search criterion is used on a column specified in ...
If any search criterion is used on other columns which ar...
Note
Note that a navigational scan incurs random page I/O, as ...
A sort operation performs an external sort of the given s...
A join can be performed either via the nested loops algor...
An inner nested loop join may contain as many streams as ...
An outer nested loops join always operates with two strea...
A sort merge operates with two input streams which are so...
Examples
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE 'RDB$%'
PLAN (RDB$RELATIONS NATURAL)
ORDER BY RDB$RELATION_NAME
SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
FROM RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF
ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
Notes
A PLAN clause may be used in all select expressions, incl...
If a PLAN clause contains some invalid retrieval descript...
ORDER <navigational_index> INDEX ( <filter_indices> ) kin...
Improvements in Sorting
A. Brinkman
Some useful improvements have been made to SQL sorting op...
Order By or Group By <alias-name>
Column aliases are now allowed in both these clauses.
Examples:
ORDER BY
SELECT RDB$RELATION_ID AS ID
FROM RDB$RELATIONS
ORDER BY ID
GROUP BY
SELECT RDB$RELATION_NAME AS ID, COUNT(*)
FROM RDB$RELATION_FIELDS
GROUP BY ID
GROUP BY Arbitrary Expressions
A GROUP BY condition can now be any valid expression.
Example
...
GROUP BY
SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
Order * Sets by Implicit Degree Number
Order by degree (ordinal column position) now works on a ...
Example
SELECT *
FROM RDB$RELATIONS
ORDER BY 9
Parameters and Ordinal Sorts--a “Gotcha”
According to grammar rules, since v.1.5, ORDER BY <value_...
However, while the DSQL parser does not reject the parame...
NEXT VALUE FOR Expression
D. Yemanov
Added SQL-99 compliant NEXT VALUE FOR <sequence_name> exp...
Examples
1.
SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
2.
INSERT INTO EMPLOYEE (ID, NAME)
VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
Note
Currently, increment ("step") values not equal to 1 (one)...
GEN_ID(<name>, 0) allows you to retrieve the current sequ...
Articles
SELECT Statement & Expression Syntax
Data Type of an Aggregation Result
A Useful Trick with Date Literals
SELECT Statement & Expression Syntax
Dmitry Yemanov
About the semantics
A select statement is used to return data to the caller (...
Select expressions retrieve parts of data that construct ...
Syntax rules
<select statement> ::=
<select expression> [FOR UPDATE] [WITH LOCK]
<select expression> ::=
<query specification> [UNION [{ALL | DISTINCT}] <quer...
<query specification> ::=
SELECT [FIRST <value>] [SKIP <value>] <select list>
FROM <table expression list>
WHERE <search condition>
GROUP BY <group value list>
HAVING <group condition>
PLAN <plan item list>
ORDER BY <sort value list>
ROWS <value> [TO <value>]
<table expression> ::=
<table name> | <joined table> | <derived table>
<joined table> ::=
{<cross join> | <qualified join>}
<cross join> ::=
<table expression> CROSS JOIN <table expression>
<qualified join> ::=
<table expression> [{INNER | {LEFT | RIGHT | FULL} [O...
ON <join condition>
<derived table> ::=
'(' <select expression> ')'
Conclusions
FOR UPDATE mode and row locking can only be performed for...
Unions are allowed inside any subquery
Clauses FIRST, SKIP, PLAN, ORDER BY, ROWS are allowed for...
Notes
Either FIRST/SKIP or ROWS is allowed, but a syntax error ...
An INSERT statement accepts a select expression to define...
UPDATE and DELETE statements are always based on an impli...
Clauses allowed at the end of UPDATE/DELETE statements ar...
Data Type of an Aggregation Result
Arno Brinkman
When aggregations, CASE evaluations and UNIONs for output...
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
In the case that
any of the data types in DTS is character string
If all data types in DTS are fixed-length character strin...
The resulting string length, in characters, is equal to t...
The character set and collation used are taken from the d...
all of the data types in DTS are exact numeric
the result data type is exact numeric with scale equal to...
any data type in DTS is approximate numeric
each data type in DTS must be numeric, otherwise an error...
any data type in DTS is a date/time data type
every data type in DTS must be a date/time type having th...
any data type in DTS is BLOB
each data type in DTS must be BLOB and all with the same ...
A Useful Trick with Date Literals
H. Borrie
In days gone by, before the advent of context variables l...
In InterBase 5.x and lower, the following statement was “...
select 'NOW' from rdb$database /* returns system date an...
In a database of ODS 10 or higher, that statement returns...
select cast('NOW' as TIMESTAMP) from rdb$database
For a long time—probably since IB 6— there has been an un...
select TIMESTAMP 'NOW' from rdb$database
This short syntax can participate in other expressions. T...
update mytable
set OVERDUE = 'T'
where DATE 'YESTERDAY' - DATE_DUE > 10
最終行:
Data Manipulation Language (DML)
Table of Contents
Quick Links
Articles
In this chapter are the additions and improvements that h...
Important
A new configuration parameter, named RelaxedAliasChecking...
This parameter will not be a permanent fixture in Firebir...
Quick Links
Common Table Expressions
The LIST Function
The RETURNING Clause
UPDATE OR INSERT Statement
MERGE Statement
New JOIN Types
INSERT with Defaults
BLOB Subtype 1 Compatibility with VARCHAR
Full Equality Comparisons Between BLOBs
RDB$DB_KEY Returns NULL in Outer Joins
Sorting on BLOB and ARRAY Columns is Restored
Built-in Functions
Functions Enhanced in V.2.0.x
DSQL Parsing of Table Names is Stricter
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
Enhancements to UNION Handling
Enhancements to NULL Logic
Subqueries and INSERT Statements Can Now Accept UNION Sets
New Extensions to UPDATE and DELETE Syntaxes
Extended Context Variables
Improvements in Handling User-specified Query Plans
Improvements in Sorting
NEXT VALUE FOR Expression
Common Table Expressions
LIST Function
RETURNING Clause
UPDATE OR INSERT Statement
MERGE Statement
New JOIN Types
NAMED COLUMNS & NATURAL JOIN
CROSS JOIN
INSERT with Defaults
Text BLOB Compatibility
Compare BLOB=BLOB
Sorting on BLOBs
RDB$DB_KEY Returns NULL in Outer Joins
New Built-in Functions
Enhancements to Built-in Functions
IIF() Expression
Improvement in CAST() Behaviour
CAST(x as <domain-name>)
Expression Arguments for SUBSTRING()
DSQL Parsing of Table Names is Stricter
EXECUTE BLOCK Statement
Derived Tables
ROLLBACK RETAIN Syntax
ROWS Syntax
UNION DISTINCT
Improved Type Coercion in UNIONs
UNIONs Allowed in ANY/ALL/IN Subqueries
New [NOT] DISTINCT Predicate
NULL Comparison Rule Relaxed
NULLs Ordering Changed
UNION Sets in Subquery Constructs
Extended Context Variables
Query Plans Improvements
GROUP or ORDER by Alias Name
GROUP BY Arbitrary Expressions
Order * Sets by Implicit Degree Number
NEXT VALUE FOR
Articles
Select Statement & Expression Syntax
Data Type of an Aggregation Result
A Useful Trick with Date Literals
Common Table Expressions
Vlad Khorsun
Based on work by Paul Ruizendaal for Fyracle project
(v.2.1) A common table expression (CTE) is like a view th...
Benefits of CTEs
Using CTEs allows you to specify dynamic queries that are...
The engine begins execution from a non-recursive member.
For each row evaluated, it starts executing each recursiv...
If the currently executing instance of a recursive member...
The memory and CPU overhead of a recursive CTE is much le...
Recursion Limit
Currently the recursion depth is limited to a hard-coded ...
Syntax and Rules for CTEs
select :
select_expr for_update_clause lock_clause
select_expr :
with_clause select_expr_body order_clause rows_clause
| select_expr_body order_clause rows_clause
with_clause :
WITH RECURSIVE with_list | WITH with_list
with_list :
with_item | with_item ',' with_list
with_item :
symbol_table_alias_name derived_column_list
AS '(' select_expr ')'
select_expr_body :
query_term
| select_expr_body UNION distinct_noise query_term
| select_expr_body UNION ALL query_term
A less formal representation:
WITH [RECURSIVE]
CTE_A [(a1, a2, …)]
AS ( SELECT … ),
CTE_B [(b1, b2, …)]
AS ( SELECT … ),
...
SELECT ...
FROM CTE_A, CTE_B, TAB1, TAB2 ...
WHERE ...
Rules for Non-Recursive CTEs
Multiple table expressions can be defined in one query
Any clause legal in a SELECT specification is legal in ta...
Table expressions can reference one another
References between expressions should not have loops
Table expressions can be used within any part of the main...
The same table expression can be used more than once in t...
Table expressions (as subqueries) can be used in INSERT, ...
Table expressions are legal in PSQL code
WITH statements can not be nested
Example of a non-recursive CTE
WITH
DEPT_YEAR_BUDGET AS (
SELECT FISCAL_YEAR, DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
)
SELECT D.DEPT_NO, D.DEPARTMENT,
B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
FROM DEPARTMENT D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996
WHERE EXISTS (
SELECT * FROM PROJ_DEPT_BUDGET B
WHERE D.DEPT_NO = B.DEPT_NO)
Rules for Recursive CTEs
A recursive CTE is self-referencing (has a reference to i...
A recursive CTE is a UNION of recursive and non-recursive...
At least one non-recursive member (anchor) must be present
Non-recursive members are placed first in the UNION
Recursive members are separated from anchor members and f...
non-recursive member (anchor)
UNION [ALL | DISTINCT]
non-recursive member (anchor)
UNION [ALL | DISTINCT]
non-recursive member (anchor)
UNION ALL
recursive member
UNION ALL
recursive member
References between CTEs should not have loops
Aggregates (DISTINCT, GROUP BY, HAVING) and aggregate fun...
A recursive member can have only one reference to itself ...
A recursive reference cannot participate in an outer join
Example of a recursive CTE
WITH RECURSIVE
DEPT_YEAR_BUDGET AS
(
SELECT FISCAL_YEAR, DEPT_NO,
SUM(PROJECTED_BUDGET) AS BUDGET
FROM PROJ_DEPT_BUDGET
GROUP BY FISCAL_YEAR, DEPT_NO
),
DEPT_TREE AS
(
SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT,
CAST('' AS VARCHAR(255)) AS INDENT
FROM DEPARTMENT
WHERE HEAD_DEPT IS NULL
UNION ALL
SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT,
H.INDENT || ' '
FROM DEPARTMENT D
JOIN DEPT_TREE H
ON D.HEAD_DEPT = H.DEPT_NO
)
SELECT D.DEPT_NO,
D.INDENT || D.DEPARTMENT AS DEPARTMENT,
B_1993.BUDGET AS B_1993,
B_1994.BUDGET AS B_1994,
B_1995.BUDGET AS B_1995,
B_1996.BUDGET AS B_1996
FROM DEPT_TREE D
LEFT JOIN DEPT_YEAR_BUDGET B_1993
ON D.DEPT_NO = B_1993.DEPT_NO
AND B_1993.FISCAL_YEAR = 1993
LEFT JOIN DEPT_YEAR_BUDGET B_1994
ON D.DEPT_NO = B_1994.DEPT_NO
AND B_1994.FISCAL_YEAR = 1994
LEFT JOIN DEPT_YEAR_BUDGET B_1995
ON D.DEPT_NO = B_1995.DEPT_NO
AND B_1995.FISCAL_YEAR = 1995
LEFT JOIN DEPT_YEAR_BUDGET B_1996
ON D.DEPT_NO = B_1996.DEPT_NO
AND B_1996.FISCAL_YEAR = 1996
The LIST Function
Oleg Loa
Dmitry Yemanov
(v.2.1) This function returns a string result with the co...
Format
<list function> ::=
LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <...
] ')'
<delimiter value> ::=
{ <string literal> | <parameter> | <variable> }
Syntax Rules
If neither ALL nor DISTINCT is specified, ALL is implied.
If <delimiter value> is omitted, a comma is used to separ...
Other Notes
Numeric and date/time values are implicitly converted to ...
The result value is of type BLOB with SUB_TYPE TEXT for a...
Ordering of values within a group is implementation-defined.
Examples
/* A */
SELECT LIST(ID, ':')
FROM MY_TABLE
/* B */
SELECT TAG_TYPE, LIST(TAG_VALUE)
FROM TAGS
GROUP BY TAG_TYPE
The RETURNING Clause
Dmitry Yemanov
Adriano dos Santos Fernandes
(v.2.1) The purpose of this SQL enhancement is to enable ...
The most likely usage is for retrieving the value generat...
In DSQL, the execution of the operation itself and the re...
Because the RETURNING clause is designed to return a sing...
Note
In DSQL, the statement always returns the set, even if th...
In PSQL, if no row was affected by the statement, nothing...
Syntax Patterns
INSERT INTO ... VALUES (...)
[RETURNING <column_list> [INTO <variable_list>]]
INSERT INTO ... SELECT ...
[RETURNING <column_list> [INTO <variable_list>]]
UPDATE OR INSERT INTO ... VALUES (...) ...
[RETURNING <column_list> [INTO <variable_list>]]
UPDATE ... [RETURNING <column_list> [INTO <variable_list>]]
DELETE FROM ...
[RETURNING <column_list> [INTO <variable_list>]]
Rules for Using a RETURNING Clause
The INTO part (i.e. the variable list) is allowed in PSQL...
The presence of the RETURNING clause causes an INSERT sta...
The RETURNING clause ignores any explicit record change (...
OLD and NEW context variables can be used in the RETURNIN...
In UPDATE and INSERT OR UPDATE statements, field referenc...
Examples
INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
RETURNING F1, F2 INTO :V1, :V2;
INSERT INTO T2 (F1, F2)
VALUES (1, 2)
RETURNING ID INTO :PK;
DELETE FROM T1
WHERE F1 = 1
RETURNING F2;
UPDATE T1
SET F2 = F2 * 10
RETURNING OLD.F2, NEW.F2;
UPDATE OR INSERT Statement
Adriano dos Santos Fernandes
(v.2.1) This syntax has been introduced to enable a recor...
Syntax Pattern
UPDATE OR INSERT INTO <table or view> [(<column_list>)]
VALUES (<value_list>)
[MATCHING (<column_list>)]
[RETURNING <column_list> [INTO <variable_list>]]
Examples
UPDATE OR INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2);
UPDATE OR INSERT INTO EMPLOYEE (ID, NAME)
VALUES (:ID, :NAME)
RETURNING ID;
UPDATE OR INSERT INTO T1 (F1, F2)
VALUES (:F1, :F2)
MATCHING (F1);
UPDATE OR INSERT INTO EMPLOYEE (ID, NAME)
VALUES (:ID, :NAME)
RETURNING OLD.NAME;
Usage Notes
When MATCHING is omitted, the existence of a primary key ...
INSERT and UPDATE permissions are needed on <table or view>.
If the RETURNING clause is present, then the statement is...
Note
A “multiple rows in singleton select” error will be raise...
MERGE Statement
Adriano dos Santos Fernandes
(v.2.1) This syntax has been introduced to enable a recor...
Syntax Pattern
<merge statement> ::=
MERGE
INTO <table or view> [ [AS] <correlation name> ]
USING <table or view or derived table> [ [AS] <correlat...
ON <condition>
[ <merge when matched> ]
[ <merge when not matched> ]
<merge when matched> ::=
WHEN MATCHED THEN
UPDATE SET <assignment list>
<merge when not matched> ::=
WHEN NOT MATCHED THEN
INSERT [ <left paren> <column list> <right paren> ]
VALUES <left paren> <value list> <right paren>
Rules for MERGE
At least one of <merge when matched> and <merge when not ...
Neither should be specified more than once.
Note
A right join is made between the INTO and USING tables us...
If no record is returned from the join, INSERT is not cal...
Example
MERGE INTO customers c
USING (SELECT * FROM customers_delta WHERE id > 10) cd
ON (c.id = cd.id)
WHEN MATCHED THEN
UPDATE SET
name = cd.name
WHEN NOT MATCHED THEN
INSERT (id, name)
VALUES (cd.id, cd.name)
New JOIN Types
Adriano dos Santos Fernandes
(v.2.1) Two new JOIN types are introduced: the NAMED COLU...
Syntax and Rules
<named columns join> ::=
<table reference> <join type> JOIN <table reference>
USING ( <column list> )
<natural join> ::=
<table reference> NATURAL <join type> JOIN <table primary>
Named columns join
All columns specified in <column list> should exist in th...
An equi-join (<left table>.<column> = <right table>.<colu...
The USING columns can be accessed without qualifiers—in t...
In “SELECT *”, USING columns are expanded once, using the...
Natural join
A “named columns join” is automatically created with all ...
If there is no common column, a CROSS JOIN is created.
Examples
/* 1 */
select * from employee
join department
using (dept_no);
/* 2 */
select * from employee_project
natural join employee
natural join project;
CROSS JOIN
D. Yemanov
(V.2.0.x) CROSS JOIN is now supported. Logically, this sy...
A CROSS JOIN B
is equivalent to either of the following:
A INNER JOIN B ON 1 = 1
or, simply:
FROM A, B
Performance Improvement at V.2.1.2
D. Yemanov
In the rare case where a cross join of three or more tabl...
INSERT with Defaults
D. Yemanov
Feature request
(v.2.1) It is now possible to INSERT without supplying va...
Example
INSERT INTO <table>
DEFAULT VALUES
[RETURNING <values>]
BLOB Subtype 1 Compatibility with VARCHAR
A. dos Santos Fernandes
(v.2.1) At various levels of evaluation, the engine now t...
Assignments, conversions and concatenations (|| operator)
Operators = , <>, >, <, >=, <=, BETWEEN, IS [NOT] DISTINC...
Functions CAST, BIT_LENGTH, CHAR[ACTER]_LENGTH, OCTET_LEN...
Note Carefully!
SUBSTRING(), when applied to a text BLOB, now returns a t...
If the FOR argument is absent, the BLOB returned will be ...
Existential predicators IN, ANY/SOME, ALL
Search predicators CONTAINING, STARTING [WITH], LIKE
Important
The predicating expression must not resolve to more than ...
A LIST expression. Note that, prior to v.2.1.4, the last ...
Full Equality Comparisons Between BLOBs
(v.2.0.x) Comparison can be performed on the entire conte...
RDB$DB_KEY Returns NULL in Outer Joins
A. dos Santos Fernandes
Feature request CORE-979
(v.2.1) By some anomaly, the physical RDB$DB_KEY has alwa...
Sorting on BLOB and ARRAY Columns is Restored
Dmitry Yemanov
(v.2.1) In earlier pre-release versions of Firebird 2.1, ...
That change was reversed in the RC2 pre-release version, ...
Important
This reversion to “bad old behaviour” does not in any way...
Built-in Functions
(v.2.1) Some existing built-in functions have been enhanc...
New Built-in Functions
Adriano dos Santos Fernandes
Oleg Loa
Alexey Karyakin
A number of built-in functions has been implemented in V....
Note
The choice between UDF and built-in function is decided w...
The new built-in function DECODE() does not have an equiv...
The functions are detailed in Appendix A.
Note
Several of these built-in functions were already availabl...
Enhancements to Functions
A. dos Santos Fernandes
EXTRACT(WEEK FROM DATE)
Feature request CORE-663
The EXTRACT() function is extended to support the ISO-860...
EXTRACT (WEEK FROM date '30.09.2007')
returns 39
alter table xyz
add WeekOfTheYear
computed by (
case
when (extract(month from CertainDate) = 12)
and (extract(week from CertainDate) = 1)
then
'Week '||extract (WEEK from CertainDate)||' of year '
|| (1 + (extract( year from CertainDate)))
else 'Week '||extract (WEEK from CertainDate)||' of y...
||extract( year from CertainDate)
end )
Specify the Scale for TRUNC()
Feature request CORE-1340
In Beta 1 the implementation of the TRUNC() function supp...
select
trunc(987.65, 1),
trunc(987.65, -1)
from rdb$database;
returns 987.60, 980.00
For other examples of using TRUNC() with and without the ...
Milliseconds Handling for EXTRACT(), DATEADD() and DATEDI...
Feature request CORE-1387
From v.2.1 Beta 2, EXTRACT(), DATEADD() and DATEDIFF() ca...
EXTRACT ( MILLISECOND FROM timestamp '01.01.2000 01:00:00...
returns 123
DATEADD ( MILLISECOND, 100, timestamp '01.01.2000 01:00:0...
DATEDIFF ( MILLISECOND, timestamp '01.01.2000 02:00:00.00...
For more explanatory examples of using DATEADD() and DATE...
Functions Enhanced in V.2.0.x
Some function enhancements were already available in the ...
IIF() Expression
O. Loa
(V.2.0.x) An IIF() expression can be used as a shortcut f...
IIF (<search_condition>, <value1>, <value2>)
is implemented as a shortcut for
CASE
WHEN <search_condition> THEN <value1>
ELSE <value2>
END
Example
SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
Improvement in CAST() Behaviour
D. Yemanov
(V.2.0.x) The infamous “Datatype unknown” error (SF Bug #...
Example
SELECT CAST(? AS INT) FROM RDB$DATABASE
CAST(x as <domain-name>)
A. dos Santos Fernandes
(V.2.1.x) Casting of compatible values or expressions can...
Syntax Pattern
CAST (<value> | <expression> AS <builtin-data-type> | <do...
Examples
CREATE DOMAIN DOM AS INTEGER;
...
SELECT CAST (10.44 AS TYPE OF DOM) AN_INTEGER
FROM RDB$DATABASE;
AN_INTEGER
----------
10
...
SELECT CAST (3.142/2 AS DOM) AN_INTEGER
FROM RDB$DATABASE;
AN_INTEGER
----------
2
Note
Directly casting to <domain-name> applies any default or ...
Expression Arguments for SUBSTRING()
O. Loa, D. Yemanov
(V.2.0.x) The built-in function SUBSTRING() can now take ...
Formerly, the inbuilt SUBSTRING() function accepted only ...
Tip
If your attempts to use this feature fail with “invalid t...
Changes to Results Returned from SUBSTRING()
(V.2.1.x) To conform with standards, the character length...
In Firebird 2.0 and 1.5, the returned value was a CHAR wi...
It is not necessary to redefine any PSQL variables you ha...
GOTCHA for BLOBs
Clearly, a text BLOB, being of indeterminate character le...
This change can break existing PSQL and expression code.
Watch out for overflows! Take particular care with CASTs ...
In v.2.1.x sub-releases prior to v.2.1.4, pay attention t...
DSQL Parsing of Table Names is Stricter
A. Brinkman
Alias handling and ambiguous field detecting have been im...
When a table alias is provided for a table, either that a...
Ambiguity checking now checks first for ambiguity at the ...
Examples
When an alias is present it must be used; or no alias at ...
This query was allowed in FB1.5 and earlier versions:
SELECT
RDB$RELATIONS.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
but will now correctly report an error that the field "RD...
Use this (preferred):
SELECT
R.RDB$RELATION_NAME
FROM
RDB$RELATIONS R
or this statement:
SELECT
RDB$RELATION_NAME
FROM
RDB$RELATIONS R
The statement below will now correctly use the FieldID fr...
UPDATE
TableA
SET
FieldA = (SELECT SUM(A.FieldB) FROM TableA A
WHERE A.FieldID = TableA.FieldID)
Note
In Firebird it is possible to provide an alias in an upda...
This example did not run correctly in Firebird 1.5 and ea...
SELECT
RDB$RELATIONS.RDB$RELATION_NAME,
R2.RDB$RELATION_NAME
FROM
RDB$RELATIONS
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = RDB$RELATIONS.RDB$RELATIO...
If RDB$RELATIONS contained 90 records, it would return 90...
This would except with a syntax error in Firebird 1.5, bu...
SELECT
(SELECT RDB$RELATION_NAME FROM RDB$DATABASE)
FROM
RDB$RELATIONS
Ambiguity checking in subqueries: the query below would r...
SELECT
(SELECT
FIRST 1 RDB$RELATION_NAME
FROM
RDB$RELATIONS R1
JOIN RDB$RELATIONS R2 ON
(R2.RDB$RELATION_NAME = R1.RDB$RELATION_NAME))
FROM
RDB$DATABASE
EXECUTE BLOCK Statement
V. Khorsun
The SQL language extension EXECUTE BLOCK makes "dynamic P...
Syntax pattern
EXECUTE BLOCK [ (param datatype = ?, param datatype = ...
[ RETURNS (param datatype, param datatype, ...) ]
AS
[DECLARE VARIABLE var datatype; ...]
BEGIN
...
END
For the client, the call isc_dsql_sql_info with the param...
isc_info_sql_stmt_select if the block has output paramete...
isc_info_sql_stmt_exec_procedure if the block has no outp...
The client should preprocess only the head of the SQL sta...
Example
The user SQL is
EXECUTE BLOCK (X INTEGER = :X)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
The preprocessed SQL is
EXECUTE BLOCK (X INTEGER = ?)
RETURNS (Y VARCHAR)
AS
DECLARE V INTEGER;
BEGIN
INSERT INTO T(...) VALUES (... :X ...);
SELECT ... FROM T INTO :Y;
SUSPEND;
END
Derived Tables
A. Brinkman
Implemented support for derived tables in DSQL (subquerie...
Syntax Pattern
SELECT
<select list>
FROM
<table reference list>
<table reference list> ::= <table reference> [{<comma...
<table reference> ::=
<table primary>
| <joined table>
<table primary> ::=
<table> [[AS] <correlation name>]
| <derived table>
<derived table> ::=
<query expression> [[AS] <correlation name>]
[<left paren> <derived column list> <right paren>]
<derived column list> ::= <column name> [{<comma> <co...
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 ag...
SELECT
DT.FIELDS,
Count(*)
FROM
(SELECT
R.RDB$RELATION_NAME,
Count(*)
FROM
RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NA...
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
Points to Note
Every column in the derived table must have a name. Unnam...
The number of columns in the column list should be the sa...
The optimizer can handle a derived table very efficiently...
ROLLBACK RETAIN Syntax
D. Yemanov
The ROLLBACK RETAIN statement is now supported in DSQL.
A “rollback retaining” feature was introduced in InterBas...
Firebird 2.0 adds an optional RETAIN clause to the DSQL R...
Syntax pattern: follows that of COMMIT RETAIN.
ROWS Syntax
D. Yemanov
ROWS syntax is used to limit the number of rows retrieved...
It is available in both DSQL and PSQL.
Syntax Pattern
SELECT ...
[ORDER BY <expr_list>]
ROWS <expr1> [TO <expr2>]
Examples
1.
SELECT * FROM T1
UNION ALL
SELECT * FROM T2
ORDER BY COL
ROWS 10 TO 100
2.
SELECT COL1, COL2,
( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
FROM T4
3.
DELETE FROM T5
ORDER BY COL5
ROWS 1
Points to Note
When <expr2> is omitted, then ROWS <expr1> is semanticall...
There is nothing that is semantically equivalent to a SKI...
Enhancements to UNION Handling
The rules for UNION queries have been improved as follows:
UNION DISTINCT Keyword Implementation
D. Yemanov
UNION DISTINCT is now allowed as a synonym for simple UNI...
Syntax Pattern
UNION [{DISTINCT | ALL}]
Improved Type Coercion in UNIONs
A. Brinkman
Automatic resolution of the data type of the result of an...
Syntax Rules
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
Case:
If any of the data types in DTS is character string, then:
If any of the data types in DTS is variable-length charac...
Otherwise, the result data type is fixed-length character...
The characterset/collation is used from the first charact...
If all of the data types in DTS are exact numeric, then t...
Note
NOTE :: Checking for precision overflows is done at run-t...
If any data type in DTS is approximate numeric, then each...
If some data type in DTS is a date/time data type, then e...
If any data type in DTS is BLOB, then each data type in D...
UNIONs Allowed in ANY/ALL/IN Subqueries
D. Yemanov
The subquery element of an ANY, ALL or IN search may now ...
Enhancements to NULL Logic
The following features involving NULL in DSQL have been i...
New [NOT] DISTINCT Test Treats Two NULL Operands as Equal
O. Loa, D. Yemanov
A new equivalence predicate behaves exactly like the equa...
Thus, IS NOT DISTINCT treats (NULL equals NULL) as if it ...
Syntax Pattern
<value> IS [NOT] DISTINCT FROM <value>
Examples
1.
SELECT * FROM T1
JOIN T2
ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
2.
SELECT * FROM T
WHERE T.MARK IS DISTINCT FROM 'test';
Points to note
Because the DISTINCT predicate considers that two NULL va...
Read More About NULL
For more understanding of the way NULL comparisons are ev...
The NOT DISTINCT predicate can be optimized using an inde...
NULL Comparison Rule Relaxed
D. Yemanov
A NULL literal can now be treated as a value in all expre...
A = NULL
B > NULL
A + NULL
B || NULL
Note
All such expressions evaluate to NULL. The change does no...
NULLs Ordering Changed to Comply with Standard
N. Samofatov
Placement of nulls in an ordered set has been changed to ...
Important
If you override the default nulls placement, no index can...
Examples
Database: proc.fdb
SQL> create table gnull(a int);
SQL> insert into gnull values(null);
SQL> insert into gnull values(1);
SQL> select a from gnull order by a;
A
============
<null>
1
SQL> select a from gnull order by a asc;
A
============
<null>
1
SQL> select a from gnull order by a desc;
A
============
1
<null>
SQL> select a from gnull order by a asc nulls first;
A
============
<null>
1
SQL> select a from gnull order by a asc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls last;
A
============
1
<null>
SQL> select a from gnull order by a desc nulls first;
A
============
<null>
1
Subqueries and INSERT Statements Can Now Accept UNION Sets
D. Yemanov
SELECT specifications used in subqueries and in INSERT IN...
New Extensions to UPDATE and DELETE Syntaxes
O. Loa
ROWS specifications and PLAN and ORDER BY clauses can now...
Users can now specify explicit plans for UPDATE/DELETE st...
Syntax Pattern
UPDATE ... SET ... WHERE ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
or
DELETE ... FROM ...
[PLAN <plan items>]
[ORDER BY <value list>]
[ROWS <value> [TO <value>]]
Extended Context Variables
A number of new facilities have been added to extend the ...
Sub-second Values Enabled for Time and DateTime Variables
D. Yemanov
CURRENT_TIMESTAMP, 'NOW' Now Return Milliseconds
The context variable CURRENT_TIMESTAMP and the date/time ...
Seconds Precision Enabled for CURRENT_TIME and CURRENT_TI...
CURRENT_TIME and CURRENT_TIMESTAMP now optionally allow s...
The feature is available in both DSQL and PSQL.
Syntax Pattern
CURRENT_TIME [(<seconds precision>)]
CURRENT_TIMESTAMP [(<seconds precision>)]
Examples
1. SELECT CURRENT_TIME FROM RDB$DATABASE;
2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
Note
The maximum possible precision is 3 which means accuracy ...
If no seconds precision is specified, the following value...
0 for CURRENT_TIME
3 for CURRENT_TIMESTAMP
New System Functions to Retrieve Context Variables
N. Samofatov
Values of context variables can now be obtained using the...
Syntax Pattern
RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
RDB$GET_CONTEXT( <namespace>, <variable> )
These functions are really a form of external function th...
Declaration
DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
VARCHAR(80),
VARCHAR(80)
RETURNS VARCHAR(255) FREE_IT;
DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
VARCHAR(80),
VARCHAR(80),
VARCHAR(255)
RETURNS INTEGER BY VALUE;
Usage
RDB$SET_CONTEXT and RDB$GET_CONTEXT set and retrieve the ...
Note
Namespace and variable names are case-sensitive.
RDB$GET_CONTEXT retrieves current value of a variable. If...
RDB$SET_CONTEXT sets a value for specific variable, if it...
To delete a variable from a context, set its value to NULL.
Pre-defined Namespaces
A fixed number of pre-defined namespaces is available:
USER_SESSION
Offers access to session-specific user-defined variables....
USER_TRANSACTION
Offers similar possibilities for individual transactions.
SYSTEM
Provides read-only access to the following variables:
NETWORK_PROTOCOL :: The network protocol used by client t...
CLIENT_ADDRESS :: The wire protocol address of the remote...
DB_NAME :: Canonical name of the current database. It is ...
ISOLATION_LEVEL :: The isolation level of the current tra...
TRANSACTION_ID :: The numeric ID of the current transacti...
SESSION_ID :: The numeric ID of the current session. The ...
CURRENT_USER :: The current user. The returned value is t...
CURRENT_ROLE :: Current role for the connection. Returns ...
Notes
To avoid DoS attacks against the Firebird Server, the num...
Example of Use
set term ^;
create procedure set_context(User_ID varchar(40), Trn_...
begin
RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_...
end ^
create table journal (
jrn_id integer not null primary key,
jrn_lastuser varchar(40),
jrn_lastaddr varchar(255),
jrn_lasttransaction integer
)^
CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UP...
as
begin
new.jrn_lastuser = rdb$get_context('USER_TRANSACTION'...
new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_...
new.jrn_lasttransaction = rdb$get_context('USER_TRANS...
end ^
commit ^
execute procedure set_context('skidder', 1) ^
insert into journal(jrn_id) values(0) ^
set term ;^
Since rdb$set_context returns 1 or zero, it can be made t...
Example
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0 means not defined already; we have set it to 'ru'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 means it was defined already; we have changed it to 'ca'
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
1
1 says it existed before; we have changed it to NULL, i.e...
SQL> select rdb$set_context('USER_SESSION', 'Nickolay',...
CNT> from rdb$database;
RDB$SET_CONTEXT
===============
0
0, since nothing actually happened this time: it was alre...
Improvements in Handling User-specified Query Plans
D. Yemanov
Plan fragments are propagated to nested levels of joins, ...
A user-supplied plan will be checked for correctness in o...
Short-circuit optimization for user-supplied plans has be...
A user-specified access path can be supplied for any SELE...
Syntax rules
The following schema describing the syntax rules should b...
PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_...
<stream_retrieval> ::= { <natural_scan> | <indexed_retrie...
<navigational_scan> }
<natural_scan> ::= <stream_alias> NATURAL
<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name>
[, <index_name> ...] )
<navigational_scan> ::= <stream_alias> ORDER <index_name>
[ INDEX ( <index_name> [, <index_name> ...] ) ]
<sorted_streams> ::= SORT ( <stream_retrieval> )
<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_r...
[, <stream_retrieval> ...] )
| [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
Details
Natural scan means that all rows are fetched in their nat...
Indexed retrieval uses an index range scan to find row id...
Navigational scan uses an index to return rows in the giv...
The index b-tree is walked from the leftmost node to the ...
If any search criterion is used on a column specified in ...
If any search criterion is used on other columns which ar...
Note
Note that a navigational scan incurs random page I/O, as ...
A sort operation performs an external sort of the given s...
A join can be performed either via the nested loops algor...
An inner nested loop join may contain as many streams as ...
An outer nested loops join always operates with two strea...
A sort merge operates with two input streams which are so...
Examples
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE 'RDB$%'
PLAN (RDB$RELATIONS NATURAL)
ORDER BY RDB$RELATION_NAME
SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
FROM RDB$RELATIONS R
JOIN RDB$RELATION_FIELDS RF
ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
Notes
A PLAN clause may be used in all select expressions, incl...
If a PLAN clause contains some invalid retrieval descript...
ORDER <navigational_index> INDEX ( <filter_indices> ) kin...
Improvements in Sorting
A. Brinkman
Some useful improvements have been made to SQL sorting op...
Order By or Group By <alias-name>
Column aliases are now allowed in both these clauses.
Examples:
ORDER BY
SELECT RDB$RELATION_ID AS ID
FROM RDB$RELATIONS
ORDER BY ID
GROUP BY
SELECT RDB$RELATION_NAME AS ID, COUNT(*)
FROM RDB$RELATION_FIELDS
GROUP BY ID
GROUP BY Arbitrary Expressions
A GROUP BY condition can now be any valid expression.
Example
...
GROUP BY
SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
Order * Sets by Implicit Degree Number
Order by degree (ordinal column position) now works on a ...
Example
SELECT *
FROM RDB$RELATIONS
ORDER BY 9
Parameters and Ordinal Sorts--a “Gotcha”
According to grammar rules, since v.1.5, ORDER BY <value_...
However, while the DSQL parser does not reject the parame...
NEXT VALUE FOR Expression
D. Yemanov
Added SQL-99 compliant NEXT VALUE FOR <sequence_name> exp...
Examples
1.
SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
2.
INSERT INTO EMPLOYEE (ID, NAME)
VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
Note
Currently, increment ("step") values not equal to 1 (one)...
GEN_ID(<name>, 0) allows you to retrieve the current sequ...
Articles
SELECT Statement & Expression Syntax
Data Type of an Aggregation Result
A Useful Trick with Date Literals
SELECT Statement & Expression Syntax
Dmitry Yemanov
About the semantics
A select statement is used to return data to the caller (...
Select expressions retrieve parts of data that construct ...
Syntax rules
<select statement> ::=
<select expression> [FOR UPDATE] [WITH LOCK]
<select expression> ::=
<query specification> [UNION [{ALL | DISTINCT}] <quer...
<query specification> ::=
SELECT [FIRST <value>] [SKIP <value>] <select list>
FROM <table expression list>
WHERE <search condition>
GROUP BY <group value list>
HAVING <group condition>
PLAN <plan item list>
ORDER BY <sort value list>
ROWS <value> [TO <value>]
<table expression> ::=
<table name> | <joined table> | <derived table>
<joined table> ::=
{<cross join> | <qualified join>}
<cross join> ::=
<table expression> CROSS JOIN <table expression>
<qualified join> ::=
<table expression> [{INNER | {LEFT | RIGHT | FULL} [O...
ON <join condition>
<derived table> ::=
'(' <select expression> ')'
Conclusions
FOR UPDATE mode and row locking can only be performed for...
Unions are allowed inside any subquery
Clauses FIRST, SKIP, PLAN, ORDER BY, ROWS are allowed for...
Notes
Either FIRST/SKIP or ROWS is allowed, but a syntax error ...
An INSERT statement accepts a select expression to define...
UPDATE and DELETE statements are always based on an impli...
Clauses allowed at the end of UPDATE/DELETE statements ar...
Data Type of an Aggregation Result
Arno Brinkman
When aggregations, CASE evaluations and UNIONs for output...
Let DTS be the set of data types over which we must deter...
All of the data types in DTS shall be comparable.
In the case that
any of the data types in DTS is character string
If all data types in DTS are fixed-length character strin...
The resulting string length, in characters, is equal to t...
The character set and collation used are taken from the d...
all of the data types in DTS are exact numeric
the result data type is exact numeric with scale equal to...
any data type in DTS is approximate numeric
each data type in DTS must be numeric, otherwise an error...
any data type in DTS is a date/time data type
every data type in DTS must be a date/time type having th...
any data type in DTS is BLOB
each data type in DTS must be BLOB and all with the same ...
A Useful Trick with Date Literals
H. Borrie
In days gone by, before the advent of context variables l...
In InterBase 5.x and lower, the following statement was “...
select 'NOW' from rdb$database /* returns system date an...
In a database of ODS 10 or higher, that statement returns...
select cast('NOW' as TIMESTAMP) from rdb$database
For a long time—probably since IB 6— there has been an un...
select TIMESTAMP 'NOW' from rdb$database
This short syntax can participate in other expressions. T...
update mytable
set OVERDUE = 'T'
where DATE 'YESTERDAY' - DATE_DUE > 10
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS