Chapter 6. Data Manipulation (DML) Statements

DML — data manipulation language — is the subset of SQL that is used by applications and procedural modules to extract and change data. Extraction, for the purpose of reading data, both raw and manipulated, is achieved with the SELECT statement. INSERT is for adding new data and DELETE is for erasing data that are no longer required. UPDATE, MERGE and UPDATE OR INSERT all modify data in various ways.

6.1 SELECT

Used forRetrieving data

Available inDSQL, ESQL, PSQL

Global syntax

1. [WITH [RECURSIVE] <cte> [, <cte> ...]]
2. SELECT
3. [FIRST m] [SKIP n]
4. [{DISTINCT | ALL}] <columns>
5. FROM
6. <source> [[AS] alias]
7. [<joins>]
8. [WHERE <condition>]
9. [GROUP BY <grouping-list>
10. [HAVING <aggregate-condition>]]
11. [WINDOW <window_definition> [, <window_definition> ...]
12. [PLAN <plan-expr>]
13. [UNION [{DISTINCT | ALL}] <other-select>]
14. [ORDER BY <ordering-list>]
15. [{ ROWS <m> [TO <n>]
16. | [OFFSET n {ROW | ROWS}]
17. [FETCH {FIRST | NEXT} [m] {ROW | ROWS} ONLY]
18. }]
19. [FOR UPDATE [OF <columns>]]
20. [WITH LOCK]
21. [INTO <variables>]

23. <variables> ::= [:]varname [, [:]varname ...]

The SELECT statement retrieves data from the database and hands them to the application or the enclosing SQL statement. Data are returned in zero or more rows, each containing one or more columns or fields. The total of rows returned is the result set of the statement.

The only mandatory parts of the SELECT statement are:

• The SELECT keyword, followed by a columns list. This part specifies what you want to retrieve.
• The FROM keyword, followed by a selectable object. This tells the engine where you want to get it from.

In its most basic form, SELECT retrieves a number of columns from a single table or view, like this:

1. select id, name, address
2. from contacts

Or, to retrieve all the columns:

1. select * from sales

In practice, a SELECT statement is usually executed with a WHERE clause, which limits the rows returned. The result set may be sorted by an ORDER BY clause, and FIRST …​ SKIP, OFFSET …​ FETCH or ROWS may further limit the number of returned rows, and can - for example - be used for pagination.

The column list may contain all kinds of expressions instead of just column names, and the source need not be a table or view: it may also be a derived table, a common table expression (CTE) or a selectable stored procedure (SP). Multiple sources may be combined in a JOIN, and multiple result sets may be combined in a UNION.

The following sections discuss the available SELECT subclauses and their usage in detail.

6.1.1 FIRST, SKIP

Used forRetrieving a slice of rows from an ordered set

Available inDSQL, PSQL

Syntax

1. SELECT
2. [FIRST <m>] [SKIP <n>]
3. FROM ...
4. ...

6. <m>, <n>  ::=
7. <integer-literal>
8. | <query-parameter>
9. | (<integer-expression>)

Table 6.1.1.1 Arguments for the FIRST and SKIP Clauses

FIRST and SKIP are non-standard syntax

FIRST and SKIP are Firebird-specific clauses. Use the SQL-standard Section 6.1.12, OFFSET, FETCH syntax wherever possible.

FIRST limits the output of a query to the first m rows. SKIP will suppress the given n rows before starting to return output.

FIRST and SKIP are both optional. When used together as in FIRST *m* SKIP *n*, the n topmost rows of the output set are discarded, and the first m rows of the rest of the set are returned.

6.1.1.1 Characteristics of FIRST and SKIP

• Any argument to FIRST and SKIP that is not an integer literal or an SQL parameter must be enclosed in parentheses. This implies that a subquery expression must be enclosed in two pairs of parentheses.
• SKIP 0 is allowed but totally pointless.
• FIRST 0 is also allowed and returns an empty set.
• Negative SKIP and/or FIRST values result in an error.
• If a SKIP lands past the end of the dataset, an empty set is returned.
• If the number of rows in the dataset (or the remainder left after a SKIP) is less than the value of the m argument supplied for FIRST, that smaller number of rows is returned. These are valid results, not error conditions.

Examples of FIRST/SKIP

1. The following query will return the first 10 names from the People table:

1. select first 10 id, name from People 2. order by name asc 2. The following query will return everything but the first 10 names:

1. select skip 10 id, name from People 2. order by name asc 3. And this one returns the last 10 rows. Notice the double parentheses:

1. select skip ((select count(*) - 10 from People)) 2. id, name from People 3. order by name asc 4. This query returns rows 81 to 100 of the People table:

1. select first 20 skip 80 id, name from People 2. order by name asc

See alsoSection 6.1.12, OFFSET, FETCH, ROWS

6.1.2 The SELECT Columns List

The columns list contains one or more comma-separated value expressions. Each expression provides a value for one output column. Alternatively, * (select star or select all) can be used to stand for all the columns in a relation (i.e. a table, view or selectable stored procedure).

Syntax

1. SELECT
2. [...]
3. [{DISTINCT | ALL}] <select_list>
4. [...]
5. FROM ...

7. <select_list> ::= * | <output_column> [, <output_column> ...]

9. <output_column> ::=
10. <qualifier>.*
11. | <value_expression> [COLLATE collation] [[AS] alias]

13. <value_expression> ::=
14. [<qualifier>.]col_name
15. | [<qualifier>.]selectable_SP_outparm
16. | <literal>
17. | <context-variable>
18. | <function-call>
19. | <single-value-subselect>
20. | <CASE-construct>
21. | any other expression returning a single
22. value of a Firebird data type or NULL

24. <qualifier> ::= a relation name or alias

26. <function-call> ::=
27. <normal_function>
28. | <aggregate_function>
29. | <window_function>

31. <normal_function> ::=
32. !! See Built-in Scalar Functions !!

34. <aggregate_function> ::=
35. !! See Aggregate Functions !!

37. <window_function> ::=
38. !! See Window Functions !!

Table 6.1.2.1 Arguments for the SELECT Columns List

It is always valid to qualify a column name (or *) with the name or alias of the table, view or selectable SP to which it belongs, followed by a dot (.). For example, relationname.columnname, relationname.*, alias.columnname, alias.*. Qualifying is required if the column name occurs in more than one relation taking part in a join. Qualifying * is always mandatory if it is not the only item in the column list.

Important

Aliases hide the original relation name: once a table, view or procedure has been aliased, only the alias can be used as its qualifier throughout the query. The relation name itself becomes unavailable.

The column list may optionally be preceded by one of the keywords DISTINCT or ALL:

• DISTINCT filters out any duplicate rows. That is, if two or more rows have the same values in every corresponding column, only one of them is included in the result set
• ALL is the default: it returns all of the rows, including duplicates. ALL is rarely used; it is supported for compliance with the SQL standard.

A COLLATE clause will not change the appearance of the column as such. However, if the specified collation changes the case or accent sensitivity of the column, it may influence:

• The ordering, if an ORDER BY clause is also present and it involves that column
• Grouping, if the column is part of a GROUP BY clause
• The rows retrieved (and hence the total number of rows in the result set), if DISTINCT is used

Examples of SELECT queries with different types of column lists

A simple SELECT using only column names:

1. select cust_id, cust_name, phone
2. from customers
3. where city = 'London'

A query featuring a concatenation expression and a function call in the columns list:

1. select 'Mr./Mrs. ' || lastname, street, zip, upper(city)
2. from contacts
3. where date_last_purchase(id) = current_date

A query with two subselects:

1. select p.fullname,
2. (select name from classes c where c.id = p.class) as class,
3. (select name from mentors m where m.id = p.mentor) as mentor
4. from pupils p

The following query accomplishes the same as the previous one using joins instead of subselects:

1. select p.fullname,
2. c.name as class,
3. m.name as mentor
4. join classes c on c.id = p.class
5. from pupils p
6. join mentors m on m.id = p.mentor

This query uses a CASE construct to determine the correct title, e.g. when sending mail to a person:

1. select case upper(sex)
2. when 'F' then 'Mrs.'
3. when 'M' then 'Mr.'
4. else ''
5. end as title,
6. lastname,
7. address
8. from employees

Query using a window function. Ranks employees by salary.

1. SELECT
2. id,
3. salary,
4. name ,
5. DENSE_RANK() OVER (ORDER BY salary) AS EMP_RANK
6. FROM employees
7. ORDER BY salary;

Querying a selectable stored procedure:

1. select * from interesting_transactions(2010, 3, 'S')
2. order by amount

Selecting from columns of a derived table. A derived table is a parenthesized SELECT statement whose result set is used in an enclosing query as if it were a regular table or view. The derived table is shown in bold here:

1. select fieldcount,
2. count(relation) as num_tables
3. from (select r.rdb$relation_name as relation,
4. count(*) as fieldcount
5. from rdb$relations r
6. join rdb$relation_fields rf
7. on rf.rdb$relation_name = r.rdb$relation_name
8. group by relation)
9. group by fieldcount

Asking the time through a context variable (CURRENT_TIME):

1. select current_time from rdb$database

For those not familiar with RDB$DATABASE: this is a system table that is present in all Firebird databases and is guaranteed to contain exactly one row. Although it wasn’t created for this purpose, it has become standard practice among Firebird programmers to select from this table if you want to select from nothing, i.e. if you need data that are not bound to a table or view, but can be derived from the expressions in the output columns alone. Another example is:

1. select power(12, 2) as twelve_squared, power(12, 3) as twelve_cubed
2. from rdb$database

Finally, an example where you select some meaningful information from RDB$DATABASE itself:

1. select rdb$character_set_name from rdb$database

As you may have guessed, this will give you the default character set of the database.

See alsoFunctions, Aggregate Functions, Window Functions, Context Variables, CASE, Subqueries

6.1.3 The FROM clause

The FROM clause specifies the source(s) from which the data are to be retrieved. In its simplest form, this is just a single table or view. However, the source can also be a selectable stored procedure, a derived table, or a common table expression. Multiple sources can be combined using various types of joins.

This section focuses on single-source selects. Joins are discussed in a following section.

Syntax

1. SELECT
2. ...
3. FROM <source>
4. [<joins>]
5. [...]

7. <source> ::=
8. { table
9. | view
10. | selectable-stored-procedure [(<args>)]
11. | <derived-table>
12. | LATERAL <derived-table>
13. | <common-table-expression>
14. } [[AS] alias]

16. <derived-table> ::=
17. (<select-statement>) [[AS] alias] [(<column-aliases>)]

19. <common-table-expression> ::=
20. WITH [RECURSIVE] <cte-def> [, <cte-def> ...]
21. <select-statement>

23. <cte-def> ::= name [(<column-aliases>)] AS (<select-statement>)

25. <column-aliases> ::= column-alias [, column-alias ...]

Table 6.1.3.1 Arguments for the FROM Clause

6.1.3.1 Selecting FROM a table or view

When selecting from a single table or view, the FROM clause requires nothing more than the name. An alias may be useful or even necessary if there are subqueries that refer to the main select statement (as they often do — subqueries like this are called correlated subqueries).

Examples

1. select id, name, sex, age from actors
2. where state = 'Ohio'

1. select * from birds
2. where type = 'flightless'
3. order by family, genus, species

1. select firstname,
2. middlename,
3. lastname,
4. date_of_birth,
5. (select name from schools s where p.school = s.id) schoolname
6. from pupils p
7. where year_started = '2012'
8. order by schoolname, date_of_birth

Never mix column names with column aliases!

If you specify an alias for a table or a view, you must always use this alias in place of the table name whenever you query the columns of the relation (and wherever else you make a reference to columns, such as ORDER BY, GROUP BY and WHERE clauses).

Correct use:

1. SELECT PEARS
2. FROM FRUIT;

4. SELECT FRUIT.PEARS
5. FROM FRUIT;

7. SELECT PEARS
8. FROM FRUIT F;

10. SELECT F.PEARS
11. FROM FRUIT F;

Incorrect use:

1. SELECT FRUIT.PEARS
2. FROM FRUIT F;

6.1.3.2 Selecting FROM a stored procedure

A selectable stored procedure is a procedure that:

• contains at least one output parameter, and
• utilizes the SUSPEND keyword so the caller can fetch the output rows one by one, just as when selecting from a table or view.

The output parameters of a selectable stored procedure correspond to the columns of a regular table.

Selecting from a stored procedure without input parameters is just like selecting from a table or view:

1. select * from suspicious_transactions
2. where assignee = 'John'

Any required input parameters must be specified after the procedure name, enclosed in parentheses:

1. select name, az, alt from visible_stars('Brugge', current_date, '22:30')
2. where alt >= 20
3. order by az, alt

Values for optional parameters (that is, parameters for which default values have been defined) may be omitted or provided. However, if you provide them only partly, the parameters you omit must all be at the tail end.

Supposing that the procedure visible_stars from the previous example has two optional parameters: min_magn (numeric(3,1)) and spectral_class (varchar(12)), the following queries are all valid:

1. select name, az, alt
2. from visible_stars('Brugge', current_date, '22:30');

4. select name, az, alt
5. from visible_stars('Brugge', current_date, '22:30', 4.0);

7. select name, az, alt
8. from visible_stars('Brugge', current_date, '22:30', 4.0, 'G');

But this one isn’t, because there’s a hole in the parameter list:

1. select name, az, alt
2. from visible_stars('Brugge', current_date, '22:30', 'G');

An alias for a selectable stored procedure is specified after the parameter list:

1. select
2. number,
3. (select name from contestants c where c.number = gw.number)
4. from get_winners('#34517', 'AMS') gw

If you refer to an output parameter (column) by qualifying it with the full procedure name, the procedure alias should be omitted:

1. select
2. number,
3. (select name from contestants c where c.number = get_winners.number)
4. from get_winners('#34517', 'AMS')

See alsoStored Procedures, CREATE PROCEDURE

6.1.3.3 Selecting FROM a derived table

A derived table is a valid SELECT statement enclosed in parentheses, optionally followed by a table alias and/or column aliases. The result set of the statement acts as a virtual table which the enclosing statement can query.

Syntax

1. (<select-query>)
2. [[AS] derived-table-alias]
3. [(<derived-column-aliases>)]

5. <derived-column-aliases> := column-alias [, column-alias ...]

The set returned data set by this SELECT FROM (SELECT FROM..) style of statement is a virtual table that can be queried within the enclosing statement, as if it were a regular table or view.

LATERAL Derived Tables

The keyword LATERAL marks a table as a lateral derived table. Lateral derived tables can reference tables (including derived tables) that occur earlier in the FROM clause. See Section 6.1.4.8, Joins with LATERAL Derived Tables for more information.

Example using a derived table

The derived table in the query below returns the list of table names in the database, and the number of columns in each table. A drill-down query on the derived table returns the counts of fields and the counts of tables having each field count:

1. SELECT
2. FIELDCOUNT,
3. COUNT(RELATION) AS NUM_TABLES
4. FROM (SELECT
5. R.RDB$RELATION_NAME RELATION,
6. COUNT(*) AS FIELDCOUNT
7. FROM RDB$RELATIONS R
8. JOIN RDB$RELATION_FIELDS RF
9. ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
10. GROUP BY RELATION)
11. GROUP BY FIELDCOUNT

A trivial example demonstrating how the alias of a derived table and the list of column aliases (both optional) can be used:

1. SELECT
2. DBINFO.DESCR, DBINFO.DEF_CHARSET
3. FROM (SELECT *
4. FROM RDB$DATABASE) DBINFO
5. (DESCR, REL_ID, SEC_CLASS, DEF_CHARSET)

More about Derived Tables

Derived tables can

• be nested
• be unions, and can be used in unions
• contain aggregate functions, subqueries and joins
• be used in aggregate functions, subqueries and joins
• be calls to selectable stored procedures or queries to them
• have WHERE, ORDER BY and GROUP BY clauses, FIRST/SKIP or ROWS directives, et al.

Furthermore,

• Each column in a derived table must have a name. If it does not have a name, such as when it is a constant or a run-time expression, it should be given an alias, either in the regular way or by including it in the list of column aliases in the derived table’s specification.

• The list of column aliases is optional but, if it exists, it must contain an alias for every column in the derived table

• The optimizer can process derived tables very effectively. However, if a derived table is included in an inner join and contains a subquery, the optimizer will be unable to use any join order.

A more useful example

Suppose we have a table COEFFS which contains the coefficients of a number of quadratic equations we have to solve. It has been defined like this:

1. create table coeffs (
2. a double precision not null,
3. b double precision not null,
4. c double precision not null,
5. constraint chk_a_not_zero check (a <> 0)
6. )

Depending on the values of a, b and c, each equation may have zero, one or two solutions. It is possible to find these solutions with a single-level query on table COEFFS, but the code will look rather messy and several values (like the discriminant) will have to be calculated multiple times per row. A derived table can help keep things clean here:

1. select
2. iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
3. iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
4. from
5. (select b, b*b - 4*a*c, 2*a from coeffs) (b, D, denom)

If we want to show the coefficients next to the solutions (which may not be a bad idea), we can alter the query like this:

1. select
2. a, b, c,
3. iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
4. iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
5. from
6. (select a, b, c, b*b - 4*a*c as D, 2*a as denom
7. from coeffs)

Notice that whereas the first query used a column aliases list for the derived table, the second adds aliases internally where needed. Both methods work, as long as every column is guaranteed to have a name.

Important

All columns in the derived table will be evaluated as many times as they are specified in the main query. This is important, as it can lead to unexpected results when using non-deterministic functions. The following shows an example of this.

1. SELECT
2. UUID_TO_CHAR(X) AS C1,
3. UUID_TO_CHAR(X) AS C2,
4. UUID_TO_CHAR(X) AS C3
5. FROM (SELECT GEN_UUID() AS X
6. FROM RDB$DATABASE) T;

The result if this query produces three different values:

1. C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
2. C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
3. C3  EB176C10-F754-4689-8B84-64B666381154

To ensure a single result of the GEN_UUID function, you can use the following method:

1. SELECT
2. UUID_TO_CHAR(X) AS C1,
3. UUID_TO_CHAR(X) AS C2,
4. UUID_TO_CHAR(X) AS C3
5. FROM (SELECT GEN_UUID() AS X
6. FROM RDB$DATABASE
7. UNION ALL
8. SELECT NULL FROM RDB$DATABASE WHERE 1 = 0) T;

This query produces a single result for all three columns:

1. C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
2. C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
3. C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

An alternative solution is to wrap the GEN_UUID query in a subquery:

1. SELECT
2. UUID_TO_CHAR(X) AS C1,
3. UUID_TO_CHAR(X) AS C2,
4. UUID_TO_CHAR(X) AS C3
5. FROM (SELECT
6. (SELECT GEN_UUID() FROM RDB$DATABASE) AS X
7. FROM RDB $ DATABASE) T;

This is an artifact of the current implementation. This behaviour may change in a future Firebird version.

6.1.3.4 Selecting FROM a Common Table Expression (CTE)

A common table expression — or CTE — is a more complex variant of the derived table, but it is also more powerful. A preamble, starting with the keyword WITH, defines one or more named CTE‘s, each with an optional column aliases list. The main query, which follows the preamble, can then access these CTE‘s as if they were regular tables or views. The CTE‘s go out of scope once the main query has run to completion.

For a full discussion of CTE‘s, please refer to the section Section 6.1.16, Common Table Expressions (WITH …​ AS …​ SELECT).

The following is a rewrite of our derived table example as a CTE:

1. with vars (b, D, denom) as (
2. select b, b*b - 4*a*c, 2*a from coeffs
3. )
4. select
5. iif (D >= 0, (-b - sqrt(D)) / denom, null) sol_1,
6. iif (D >  0, (-b + sqrt(D)) / denom, null) sol_2
7. from vars

Except for the fact that the calculations that have to be made first are now at the beginning, this isn’t a great improvement over the derived table version. However, we can now also eliminate the double calculation of sqrt(D) for every row:

1. with vars (b, D, denom) as (
2. select b, b*b - 4*a*c, 2*a from coeffs
3. ),
4. vars2 (b, D, denom, sqrtD) as (
5. select b, D, denom, iif (D >= 0, sqrt(D), null) from vars
6. )
7. select
8. iif (D >= 0, (-b - sqrtD) / denom, null) sol_1,
9. iif (D >  0, (-b + sqrtD) / denom, null) sol_2
10. from vars2

The code is a little more complicated now, but it might execute more efficiently (depending on what takes more time: executing the SQRT function or passing the values of b, D and denom through an extra CTE). Incidentally, we could have done the same with derived tables, but that would involve nesting.

Important

All columns in the CTE will be evaluated as many times as they are specified in the main query. This is important, as it can lead to unexpected results when using non-deterministic functions. The following shows an example of this.

1. WITH T (X) AS (
2. SELECT GEN_UUID()
3. FROM RDB$DATABASE)
4. SELECT
5. UUID_TO_CHAR(X) as c1,
6. UUID_TO_CHAR(X) as c2,
7. UUID_TO_CHAR(X) as c3
8. FROM T

The result if this query produces three different values:

1. C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
2. C2  C1214CD3-423C-406D-B5BD-95BF432ED3E3
3. C3  EB176C10-F754-4689-8B84-64B666381154

To ensure a single result of the GEN_UUID function, you can use the following method:

1. WITH T (X) AS (
2. SELECT GEN_UUID()
3. FROM RDB$DATABASE
4. UNION ALL
5. SELECT NULL FROM RDB$DATABASE WHERE 1 = 0)
6. SELECT
7. UUID_TO_CHAR(X) as c1,
8. UUID_TO_CHAR(X) as c2,
9. UUID_TO_CHAR(X) as c3
10. FROM T;

This query produces a single result for all three columns:

1. C1  80AAECED-65CD-4C2F-90AB-5D548C3C7279
2. C2  80AAECED-65CD-4C2F-90AB-5D548C3C7279
3. C3  80AAECED-65CD-4C2F-90AB-5D548C3C7279

An alternative solution is to wrap the GEN_UUID query in a subquery:

1. WITH T (X) AS (
2. SELECT (SELECT GEN_UUID() FROM RDB$DATABASE)
3. FROM RDB$DATABASE)
4. SELECT
5. UUID_TO_CHAR(X) as c1,
6. UUID_TO_CHAR(X) as c2,
7. UUID_TO_CHAR(X) as c3
8. FROM T;

This is an artifact of the current implementation. This behaviour may change in a future Firebird version.

See alsoSection 6.1.16, Common Table Expressions (WITH …​ AS …​ SELECT).

6.1.4 Joins

Joins combine data from two sources into a single set. This is done on a row-by-row basis and usually involves checking a join condition in order to determine which rows should be merged and appear in the resulting dataset. There are several types (INNER, OUTER) and classes (qualified, natural, etc.) of joins, each with its own syntax and rules.

Since joins can be chained, the datasets involved in a join may themselves be joined sets.

Syntax

1. SELECT
2. ...
3. FROM <source>
4. [<joins>]
5. [...]

7. <source> ::=
8. { table
9. | view
10. | selectable-stored-procedure [(<args>)]
11. | <derived-table>
12. | LATERAL <derived-table>
13. | <common-table-expression>
14. } [[AS] alias]

16. <joins> ::= <join> [<join> ...]

18. <join> ::=
19. [<join-type>] JOIN <source> <join-condition>
20. | NATURAL [<join-type>] JOIN <source>
21. | {CROSS JOIN | ,} <source>

23. <join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

25. <join-condition> ::= ON <condition> | USING (<column-list>)

Table 6.1.4.1 Arguments for JOIN Clauses

6.1.4.1 Inner vs. Outer Joins

A join always combines data rows from two sets (usually referred to as the left set and the right set). By default, only rows that meet the join condition (i.e. that match at least one row in the other set when the join condition is applied) make it into the result set. This default type of join is called an inner join. Suppose we have the following two tables:

Table 6.1.4.1.1 Table A

Table 6.1.4.1.2 Table B

If we join these tables like this:

1. select *
2. from A
3. join B on A.id = B.code;

then the result set will be:

The first row of A has been joined with the second row of B because together they met the condition A.id = B.code. The other rows from the source tables have no match in the opposite set and are therefore not included in the join. Remember, this is an INNER join. We can make that fact explicit by writing:

1. select *
2. from A
3. inner join B on A.id = B.code;

However, since INNER is the default, it is usually ommitted.

It is perfectly possible that a row in the left set matches several rows from the right set or vice versa. In that case, all those combinations are included, and we can get results like:

Sometimes we want (or need) all the rows of one or both of the sources to appear in the joined set, regardless of whether they match a record in the other source. This is where outer joins come in. A LEFT outer join includes all the records from the left set, but only matching records from the right set. In a RIGHT outer join it’s the other way around. FULL outer joins include all the records from both sets. In all outer joins, the holes (the places where an included source record doesn’t have a match in the other set) are filled up with NULLs.

In order to make an outer join, you must specify LEFT, RIGHT or FULL, optionally followed by the keyword OUTER.

Below are the results of the various outer joins when applied to our original tables A and B:

1. select *
2. from A
3. left [outer] join B on A.id = B.code;

1. select *
2. from A
3. right [outer] join B on A.id = B.code

1. select *
2. from A
3. full [outer] join B on A.id = B.code

6.1.4.2 Qualified joins

Qualified joins specify conditions for the combining of rows. This happens either explicitly in an ON clause or implicitly in a USING clause.

Syntax

1. <qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

3. <join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

5. <join-condition> ::= ON <condition> | USING (<column-list>)

6.1.4.2.1 Explicit-condition joins

Most qualified joins have an ON clause, with an explicit condition that can be any valid Boolean expression, but usually involves some comparison between the two sources involved.

Quite often, the condition is an equality test (or a number of ANDed equality tests) using the = operator. Joins like these are called equi-joins. (The examples in the section on inner and outer joins were al equi-joins.)

Examples of joins with an explicit condition:

1. /* Select all Detroit customers who made a purchase
2. in 2013, along with the purchase details: */
3. select * from customers c
4. join sales s on s.cust_id = c.id
5. where c.city = 'Detroit' and s.year = 2013;

1. /* Same as above, but include non-buying customers: */
2. select * from customers c
3. left join sales s on s.cust_id = c.id
4. where c.city = 'Detroit' and s.year = 2013;

1. /* For each man, select the women who are taller than he.
2. Men for whom no such woman exists are not included. */
3. select m.fullname as man, f.fullname as woman
4. from males m
5. join females f on f.height > m.height;

1. /* Select all pupils with their class and mentor.
2. Pupils without a mentor are also included.
3. Pupils without a class are not included. */
4. select p.firstname, p.middlename, p.lastname,
5. c.name, m.name
6. from pupils p
7. join classes c on c.id = p.class
8. left join mentors m on m.id = p.mentor;

6.1.4.2.2 Named columns joins

Equi-joins often compare columns that have the same name in both tables. If this is the case, we can also use the second type of qualified join: the named columns join.

Note

Named columns joins are not supported in Dialect 1 databases.

Named columns joins have a USING clause which states just the column names. So instead of this:

1. select * from flotsam f
2. join jetsam j
3. on f.sea = j.sea
4. and f.ship = j.ship;

we can also write:

1. select * from flotsam
2. join jetsam using (sea, ship)

which is considerably shorter. The result set is a little different though — at least when using SELECT *:

• The explicit-condition join — with the ON clause — will contain each of the columns SEA and SHIP twice: once from table FLOTSAM, and once from table JETSAM. Obviously, they will have the same values.
• The named columns join — with the USING clause — will contain these columns only once.

If you want all the columns in the result set of the named columns join, set up your query like this:

1. select f.*, j.*
2. from flotsam f
3. join jetsam j using (sea, ship);

This will give you the exact same result set as the explicit-condition join.

For an OUTER named columns join, there’s an additional twist when using SELECT * or an unqualified column name from the USING list:

If a row from one source set doesn’t have a match in the other but must still be included because of the LEFT, RIGHT or FULL directive, the merged column in the joined set gets the non-NULL value. That is fair enough, but now you can’t tell whether this value came from the left set, the right set, or both. This can be especially deceiving when the value came from the right hand set, because * always shows combined columns in the left hand part — even in the case of a RIGHT join.

Whether this is a problem or not depends on the situation. If it is, use the a.*, b.* approach shown above, with a and b the names or aliases of the two sources. Or better yet, avoid * altogether in your serious queries and qualify all column names in joined sets. This has the additional benefit that it forces you to think about which data you want to retrieve and where from.

It is your responsibility to make sure the column names in the USING list are of compatible types between the two sources. If the types are compatible but not equal, the engine converts them to the type with the broadest range of values before comparing the values. This will also be the data type of the merged column that shows up in the result set if SELECT * or the unqualified column name is used. Qualified columns on the other hand will always retain their original data type.

Tip

If, when joining by named columns, you are using a join column in the WHERE clause, always use the qualified column name, otherwise an index on this column will not be used.

1. SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE x = 0;

3. -- PLAN JOIN (A NATURAL , B INDEX (RDB$2))

However:

1. SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE a.x = 0;
2. -- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

4. SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE b.x = 0;
5. -- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

The fact is, the unspecified column in this case is implicitly replaced by `COALESCE(a.x, b.x). This clever trick is used to disambiguate column names, but it also interferes with the use of the index.

6.1.4.3 Natural joins

Taking the idea of the named columns join a step further, a natural join performs an automatic equi-join on all the columns that have the same name in the left and right table. The data types of these columns must be compatible.

Note

Natural joins are not supported in Dialect 1 databases.

Syntax

1. <natural-join> ::= NATURAL [<join-type>] JOIN <source>

3. <join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Given these two tables:

1. create table TA (
2. a bigint,
3. s varchar(12),
4. ins_date date
5. );

1. create table TB (
2. a bigint,
3. descr varchar(12),
4. x float,
5. ins_date date
6. );

A natural join on TA and TB would involve the columns a and ins_date, and the following two statements would have the same effect:

1. select * from TA
2. natural join TB;

1. select * from TA
2. join TB using (a, ins_date);

Like all joins, natural joins are inner joins by default, but you can turn them into outer joins by specifying LEFT, RIGHT or FULL before the JOIN keyword.

Caution

If there are no columns with the same name in the two source relations, a CROSS JOIN is performed. We’ll get to this type of join in a minute.

6.1.4.4 Cross joins

A cross join produces the full set product of the two data sources. This means that it successfully matches every row in the left source to every row in the right source.

Syntax

1. <cross-join> ::= {CROSS JOIN | ,} <source>

Please notice that the comma syntax is deprecated! It is only supported to keep legacy code working and may disappear in some future version.

Cross-joining two sets is equivalent to joining them on a tautology (a condition that is always true). The following two statements have the same effect:

1. select * from TA
2. cross join TB;

1. select * from TA
2. join TB on 1 = 1;

Cross joins are inner joins, because they only include matching records – it just so happens that every record matches! An outer cross join, if it existed, wouldn’t add anything to the result, because what outer joins add are non-matching records, and these don’t exist in cross joins.

Cross joins are seldom useful, except if you want to list all the possible combinations of two or more variables. Suppose you are selling a product that comes in different sizes, different colors and different materials. If these variables are each listed in a table of their own, this query would return all the combinations:

1. select m.name, s.size, c.name
2. from materials m
3. cross join sizes s
4. cross join colors c;

6.1.4.4.1 Implicit Joins

In the SQL:89 standard, the tables involved in a join were specified as a comma-delimited list in the FROM clause (in other words, a cross join). The join conditions were then specified in the WHERE clause among other search terms. This type of join is called an implicit join.

An example of an implicit join:

1. /*
2. * A sample of all Detroit customers who
3. * made a purchase.
4. */
5. SELECT *
6. FROM customers c, sales s
7. WHERE s.cust_id = c.id AND c.city = 'Detroit'

Important

The implicit join syntax is deprecated and may be removed in a future version. We recommend using the explicit join syntax shown earlier.

6.1.4.4.2 Mixing Explicit and Implicit Joins

Mixing explicit and implicit joins is not recommend, but is allowed. However, some types of mixing are not supported by Firebird.

For example, the following query will raise the error Column does not belong to referenced table

1. SELECT *
2. FROM TA, TB
3. JOIN TC ON TA.COL1 = TC.COL1
4. WHERE TA.COL2 = TB.COL2

That is because the explicit join cannot see the TA table. However, the next query will complete without error, since the restriction is not violated.

1. SELECT *
2. FROM TA, TB
3. JOIN TC ON TB.COL1 = TC.COL1
4. WHERE TA.COL2 = TB.COL2

6.1.4.5 A Note on Equality

Important

This note about equality and inequality operators applies everywhere in Firebird’s SQL language, not just in JOIN conditions.

The = operator, which is explicitly used in many conditional joins and implicitly in named column joins and natural joins, only matches values to values. According to the SQL standard, NULL is not a value and hence two NULLs are neither equal nor unequal to one another. If you need NULLs to match each other in a join, use the IS NOT DISTINCT FROM operator. This operator returns true if the operands have the same value or if they are both NULL.

1. select *
2. from A join B
3. on A.id is not distinct from B.code;

Likewise, in the — extremely rare — cases where you want to join on inequality, use IS DISTINCT FROM, not <>, if you want NULL to be considered different from any value and two NULLs considered equal:

1. select *
2. from A join B
3. on A.id is distinct from B.code;

6.1.4.6 Ambiguous field names in joins

Firebird rejects unqualified field names in a query if these field names exist in more than one dataset involved in a join. This is even true for inner equi-joins where the field name figures in the ON clause like this:

1. select a, b, c
2. from TA
3. join TB on TA.a = TB.a;

There is one exception to this rule: with named columns joins and natural joins, the unqualified field name of a column taking part in the matching process may be used legally and refers to the merged column of the same name. For named columns joins, these are the columns listed in the USING clause. For natural joins, they are the columns that have the same name in both relations. But please notice again that, especially in outer joins, plain colname isn’t always the same as left.colname or right.colname. Types may differ, and one of the qualified columns may be NULL while the other isn’t. In that case, the value in the merged, unqualified column may mask the fact that one of the source values is absent.

6.1.4.7 Joins with stored procedures

If a join is performed with a stored procedure that is not correlated with other data streams via input parameters, there are no oddities. If correlation is involved, an unpleasant quirk reveals itself. The problem is that the optimizer denies itself any way to determine the interrelationships of the input parameters of the procedure from the fields in the other streams:

1. SELECT *
2. FROM MY_TAB
3. JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

Here, the procedure will be executed before a single record has been retrieved from the table, MY_TAB. The isc_no_cur_rec error error (no current record for fetch operation) is raised, interrupting the execution.

The solution is to use syntax that specifies the join order explicitly:

1. SELECT *
2. FROM MY_TAB
3. LEFT JOIN MY_PROC(MY_TAB.F) ON 1 = 1;

This forces the table to be read before the procedure and everything works correctly.

Tip

This quirk has been recognised as a bug in the optimizer and will be fixed in the next version of Firebird.

6.1.4.8 Joins with LATERAL Derived Tables

A derived table defined with the LATERAL keyword is called a lateral derived table. If a derived table is defined as lateral, then it is allowed to refer to other tables in the same FROM clause, but only those declared before it in the FROM clause.

Lateral Derived Table Examples

``` 1. / select customers with their last order date and number / 2. select c.name, ox.order_date as last_order, ox.number 3. from customer c 4. left join LATERAL ( 5. select first 1 o.order_date, o.number 6. from orders o 7. where o.id_customer = c.id 8. order by o.ORDER_DATE desc 9. ) as ox on true 10. -- 11. select dt.population, dt.city_name, c.country_name 12. from (select distinct country_name from cities) AS c 13. cross join LATERAL ( 14. select first 1 city_name, population 15. from cities 16. where cities.country_name = c.country_name 17. order by population desc 18. ) AS dt; 19. -- 20. select salespeople.name, 21. max_sale.amount, 22. customer_of_max_sale.customer_name 23. from salespeople, 24. LATERAL ( select max(amount) as amount 25. from all_sales 26. where all_sales.salesperson_id = salespeople.id 27. ) as max_sale, 28. LATERAL ( select customer_name 29. from all_sales 30. where all_sales.salesperson_id = salespeople.id 31. and all_sales.amount = max_sale.amount 32.

（本文超出站点单文档体积上限，已截断）