Chapter 4Common Language Elements

This chapter covers the elements that are common throughout the implementation of the SQL language — the expressions that are used to extract and operate on conditions about data and the predicates that test the truth of those assertions.

4.1Expressions

SQL expressions provide formal methods for evaluating, transforming and comparing values. SQL expressions may include table columns, variables, constants, literals, various statements and predicates and also other expressions. The complete list of possible tokens in expressions follows.

Description of Expression Elements
Column name

Identifier of a column from a specified table used in evaluations or as a search condition. A column of the array type cannot be an element in an expression except when used with the IS [NOT] NULL predicate.

Array element

An expression may contain a reference to an array member i.e., <array_name>[s], where s is the subscript of the member in the array <array_name>

Arithmetic operators

The +, -, *, / characters used to calculate values

Concatenation operator

The || (double-pipe) operator used to concatenate strings

Logical operators

The reserved words NOT, AND and OR, used to combine simple search conditions in order to create complex conditions

Comparison operators

The symbols =, <>, !=, ~=, ^=, <, <=, >, >=, !<, ~<, ^<, !>, ~> and ^>

Comparison predicates

LIKE, STARTING WITH, CONTAINING, SIMILAR TO, BETWEEN, IS [NOT] NULL, IS [NOT] {TRUE | FALSE | UNKNOWN} and IS [NOT] DISTINCT FROM

Existential predicates

Predicates used to check the existence of values in a set. The IN predicate can be used both with sets of comma-separated constants and with subqueries that return a single column. The EXISTS, SINGULAR, ALL, ANY and SOME predicates can be used only with subqueries.

Constant or Literal

Numbers, or string literals enclosed in apostrophes, Boolean values TRUE, FALSE and UNKOWN, NULL

Datetime literal

An expression, similar to a string literal enclosed in apostrophes, that can be interpreted as a date, time or timestamp value. Datetime literals can be strings of characters and numerals, such as TIMESTAMP '25.12.2016 15:30:35', that can be resolved as datetime value, also used for string literals that can be cast to a datetime type.

Datetime mnemonics

A string literal with a description of a desired datetime value that can be cast to a datetime type. For example 'TODAY', 'NOW'.

Context variable

An internally-defined context variable

Local variable

Declared local variable, input or output parameter of a PSQL module (stored procedure, trigger, unnamed PSQL block in DSQL)

Positional parameter

A member of in an ordered group of one or more unnamed parameters passed to a stored procedure or prepared query

Subquery

A SELECT statement enclosed in parentheses that returns a single (scalar) value or, when used in existential predicates, a set of values

Function identifier

The identifier of an internal or external function in a function expression

Type cast

An expression explicitly converting data of one data type to another using the CAST function ( CAST (<value> AS <datatype>) ). For date/time literals only, the shorthand syntax <datatype> <value> is also supported (DATE '2016-12-25').

Conditional expression

Expressions using CASE and related internal functions

Parentheses

Bracket pairs (…​) used to group expressions. Operations inside the parentheses are performed before operations outside them. When nested parentheses are used, the most deeply nested expressions are evaluated first and then the evaluations move outward through the levels of nesting.

COLLATE clause

Clause applied to CHAR and VARCHAR types to specify the character-set-specific collation sequence to use in string comparisons

NEXT VALUE FOR sequence

Expression for obtaining the next value of a specified generator (sequence). The internal GEN_ID() function does the same.

AT expression

Expression to change the time zone of a datetime.

4.1.1Literals (Constants)

A literal — or constant — is a value that is supplied directly in an SQL statement, not derived from an expression, a parameter, a column reference nor a variable. It can be a string or a number.

4.1.1.1String Literals

A string literal is a series of characters enclosed between a pair of apostrophes (single quotes). The maximum length of a string literal is 32,765 for CHAR/VARCHAR, or 65,533 bytes for BLOB; the maximum character count will be determined by the number of bytes used to encode each character.

Note
  • Double quotes are NOT VALID for quoting strings. The SQL standard reserves double quotes for a different purpose: quoting identifiers.

  • If a literal apostrophe is required within a string constant, it is escaped by prefixing it with another apostrophe. For example, 'Mother O''Reilly''s home-made hooch'. Or use the alternative quote literal: q'{Mother O'Reilly's home-made hooch}'

  • Care should be taken with the string length if the value is to be written to a CHAR or VARCHAR column. The maximum length for a CHAR or VARCHAR` literal is 32,765 bytes.

The character set of a string constant is assumed to be the same as the character set of its destined storage.

4.1.1.1.1String Literals in Hexadecimal Notation

From Firebird 2.5 forward, string literals can be entered in hexadecimal notation, so-called binary strings. Each pair of hex digits defines one byte in the string. Strings entered this way will be type BINARY (or character set OCTETS) by default, unless the introducer syntax is used to force a string to be interpreted as another character set.

Syntax

  |{x|X}'<hexstring>'
  | 
  |<hexstring>  ::=  an even number of <hexdigit>
  |<hexdigit>   ::=  one of 0..9, A..F, a..f

Examples

   |select x'4E657276656E' from rdb$database
   |-- returns 4E657276656E, a 6-byte 'binary' string
   | 
   |select _ascii x'4E657276656E' from rdb$database
   |-- returns 'Nerven' (same string, now interpreted as ASCII text)
   | 
   |select _iso8859_1 x'53E46765' from rdb$database
   |-- returns 'Säge' (4 chars, 4 bytes)
   | 
   |select _utf8 x'53C3A46765' from rdb$database
   |-- returns 'Säge' (4 chars, 5 bytes)

Notes

The client interface determines how binary strings are displayed to the user. The isql utility, for example, uses upper case letters A-F, while FlameRobin uses lower case letters. Other client programs may use other conventions, such as displaying spaces between the byte pairs: '4E 65 72 76 65 6E'.

The hexadecimal notation allows any byte value (including 00) to be inserted at any position in the string. However, if you want to coerce it to anything other than OCTETS, it is your responsibility to supply the bytes in a sequence that is valid for the target character set.

4.1.1.1.2Alternative String Literals

Since Firebird 3.0, it is possible to use a character, or character pair, other than the doubled (escaped) apostrophe, to embed a quoted string inside another string without the need to escape the quote. The keyword q or Q preceding a quoted string informs the parser that certain left-right pairs or pairs of identical characters within the string are the delimiters of the embedded string literal.

Syntax

  |<alternative string literal> ::=
  |    { q | Q } <quote> <start char> [<char> ...] <end char> <quote>

Rules

When <start char> is (, {, [ or <, <end char> is paired up with its respective partner, viz. ), }, ] and >. In other cases, <end char> is the same as <start char>.

Inside the string, i.e. <char> items, single (not escaped) quotes can be used. Each quote will be part of the result string.

Examples

  |select q'{abc{def}ghi}' from rdb$database;        -- result: abc{def}ghi
  |select q'!That's a string!' from rdb$database;    -- result: That's a string

4.1.1.1.3Introducer Syntax for String Literals

If necessary, a string literal may be preceded by a character set name, itself prefixed with an underscore _. This is known as introducer syntax. Its purpose is to inform the engine about how to interpret and store the incoming string.

Example

  |INSERT INTO People
  |VALUES (_ISO8859_1 'Hans-Jörg Schäfer')

4.1.1.2Number Literals

A number literal is any valid number in a supported notation:

  • In SQL, for numbers in the standard decimal notation, the decimal point is always represented by period character (., full-stop, dot); thousands are not separated. Inclusion of commas, blanks, etc. will cause errors.

  • Exponential notation is supported. For example, 0.0000234 can be expressed as 2.34e-5.

  • Hexadecimal notation is supported by Firebird 2.5 and higher versions — see below.

The format of the literal decides the type (<d> for a decimal digit, <h> for a hexadecimal digit):

FormatType

<d>[<d> …​]

INTEGER, BIGINT, INT128 or DECFLOAT(34) (depends on if value fits in the type). DECFLOAT(34) is used for values that do not fit in INT128.

0{x|X} <h><h>[<h><h> …​]

INTEGER for 1-4 <h><h> pairs or BIGINT for 5-8 pairs

<d>[<d> …​] "." [<d> …​]

NUMERIC(18, n), NUMERIC(38, n) or DECFLOAT(34) where n depends on the number of digits after the decimal point, and precision on the total number of digits.

For backwards compatibility, some values of 19 digits are mapped to NUMERIC(18,2). DECFLOAT(34) is used when the unscaled value does not fit in INT128.

<d>[<d> …​]["." [<d> …​]] E <d>[<d> …​]

DOUBLE PRECISION or DECFLOAT(34), where DECFLOAT is used only if number of digits is 20 or higher, or the absolute exponent is 309 or greater.

4.1.1.2.1Hexadecimal Notation for Numbers

From Firebird 2.5 forward, integer values can be entered in hexadecimal notation. Numbers with 1-8 hex digits will be interpreted as type INTEGER; numbers with 9-16 hex digits as type BIGINT; numbers with 17-32 hex digits as type INT128.

Syntax

  |0{x|X}<hexdigits>
  | 
  |<hexdigits>  ::=  1-32 of <hexdigit>
  |<hexdigit>   ::=  one of 0..9, A..F, a..f

Examples

  |select 0x6FAA0D3 from rdb$database           -- returns 117088467
  |select 0x4F9 from rdb$database               -- returns 1273
  |select 0x6E44F9A8 from rdb$database          -- returns 1850014120
  |select 0x9E44F9A8 from rdb$database          -- returns -1639646808 (an INTEGER)
  |select 0x09E44F9A8 from rdb$database         -- returns 2655320488 (a BIGINT)
  |select 0x28ED678A4C987 from rdb$database     -- returns 720001751632263
  |select 0xFFFFFFFFFFFFFFFF from rdb$database  -- returns -1

4.1.1.2.1.1Hexadecimal Value Ranges
  • Hex numbers in the range 0 .. 7FFF FFFF are positive INTEGERs with values between 0 .. 2147483647 decimal. To coerce a number to BIGINT, prepend enough zeroes to bring the total number of hex digits to nine or above. That changes the type but not the value.

  • Hex numbers between 8000 0000 .. FFFF FFFF require some attention:

    • When written with eight hex digits, as in 0x9E44F9A8, a value is interpreted as 32-bit INTEGER. Since the leftmost bit (sign bit) is set, it maps to the negative range -2147483648 .. -1 decimal.

    • With one or more zeroes prepended, as in 0x09E44F9A8, a value is interpreted as 64-bit BIGINT in the range 0000 0000 8000 0000 .. 0000 0000 FFFF FFFF. The sign bit is not set now, so they map to the positive range 2147483648 .. 4294967295 decimal.

    Thus, in this range — and only in this range — prepending a mathematically insignificant 0 results in a totally different value. This is something to be aware of.

  • Hex numbers between 1 0000 0000 .. 7FFF FFFF FFFF FFFF are all positive BIGINT.

  • Hex numbers between 8000 0000 0000 0000 .. FFFF FFFF FFFF FFFF are all negative BIGINT.

  • A SMALLINT cannot be written in hex, strictly speaking, since even 0x1 is evaluated as INTEGER. However, if you write a positive integer within the 16-bit range 0x0000 (decimal zero) to 0x7FFF (decimal 32767) it will be converted to SMALLINT transparently.

    It is possible to write to a negative SMALLINT in hex, using a 4-byte hex number within the range 0xFFFF8000 (decimal -32768) to 0xFFFFFFFF (decimal -1).

4.1.1.3Boolean Literals

A Boolean literal is one of TRUE, FALSE or UNKNOWN.

4.1.1.4Datetime Literals

Formally, the SQL standard defines datetime literals as a prefix DATE, TIME and TIMESTAMP followed by a string literal with a datetime format. Historically, Firebird documentation has referred to these datetime literals as "shorthand casts".

In Firebird 4.0, the use of datetime mnemonics in datetime literals (e.g. DATE 'TODAY') is no longer allowed.

Caution

The format of datetime literals and strings in Firebird 4.0 is more strict compared to earlier Firebird versions.

Datetime Literal Syntax

  |<datetime_literal> ::=
  |    DATE '<date_format>'
  |  | TIME { '<time_format>' | '<time_tz_format>' }
  |  | TIMESTAMP { '<timestamp_format>' | '<timestamp_tz_format>' }

Datetime Format Syntax

   |<date_format> ::=
   |      [YYYY<p>]MM<p>DD
   |    | MM<p>DD[<p>{ YYYY | YY }]
   |    | DD<p>MM[<p>{ YYYY | YY }]
   | 
   |<time_format> ::= HH[:mm[:SS[<f>NNNN]]]
   | 
   |<timestamp_format> ::= <date_format> [<space> <time_format>]
   | 
   |<time_zone> ::=
   |    { + | - }HH:MM
   |  | time zone name (e.g. Europe/Berlin)
   | 
   |<time_tz_format> ::= <time_format> [<space>] <time_zone>
   | 
   |<timestamp_tz_format> ::= <timestamp_format> [<space>] <time_zone>
   | 
   |<p> ::= whitespace | . | - | /
   |<f> ::= : | .

Table 4.1Date and Time Literal Format Arguments
ArgumentDescription

datetime_literal

Datetime literal

date_format

Format of date

time_format

Format of time

timestamp_format

Format of timestamp

time_zone

Format of time zone

time_tz_format

Format of time with time zone

timestamp_tz_format

Format of timestamp with time zone

YYYY

Four-digit year

YY

Two-digit year

MM

Month It may contain 1 or 2 digits (1-12 or 01-12). You can also specify the three-letter shorthand name or the full name of a month in English. Case-insensitive

DD

Day. It may contain 1 or 2 digits (1-31 or 01-31)

HH

Hour. It may contain 1 or 2 digits (0-23 or 00-23)

mm

Minutes. It may contain 1 or 2 digits (0-59 or 00-59)

SS

Seconds. It may contain 1 or 2 digits (0-59 or 00-59)

NNNN

Ten-thousandths of a second. It may contain from 1 to 4 digits (0-9999)

p

A date separator, any of permitted characters. Leading and trailing spaces are ignored. The choice of separator in a date decides whether the parser reads MM<p>DD or DD<p>MM.

f

Fractional seconds separator

Important

Use of the complete specification of the year in the four-digit form — YYYY — is strongly recommended, to avoid confusion in date calculations and aggregations.

Example

   |-- 1
   |  UPDATE PEOPLE
   |  SET AGECAT = 'SENIOR'
   |  WHERE BIRTHDATE < DATE '1-Jan-1943';
   |-- 2
   |  INSERT INTO APPOINTMENTS
   |  (EMPLOYEE_ID, CLIENT_ID, APP_DATE, APP_TIME)
   |  VALUES (973, 8804, DATE '1-Jan-2021' + 2, TIME '16:00');
   |-- 3
   |  NEW.LASTMOD = TIMESTAMP '1-Jan-2021 16:00';

Tip

Although the Firebird datetime syntax is very flexible, to avoid ambiguity we recommend using the ISO-8601 order (year-month-day), - as the date separator, 4 digits for year, 2 digits for month, day, minute and second, and . as second.fractions separator. This format is also the only one defined in the SQL standard.

In short, use TIMESTAMP '2021-05-03 04:05:00.1 +02:00', not TIMESTAMP '3.5.21 4:5:0:1 +2:0'.

4.1.2SQL Operators

SQL operators comprise operators for comparing, calculating, evaluating and concatenating values.

4.1.2.1Operator Precedence

SQL Operators are divided into four types. Each operator type has a precedence, a ranking that determines the order in which operators and the values obtained with their help are evaluated in an expression. The higher the precedence of the operator type is, the earlier it will be evaluated. Each operator has its own precedence within its type, that determines the order in which they are evaluated in an expression.

Operators with the same precedence are evaluated from left to right. To force a different evaluation order, operations can be grouped by means of parentheses.

Table 4.2Operator Type Precedence
Operator TypePrecedenceExplanation

Concatenation

1

Strings are concatenated before any other operations take place

Arithmetic

2

Arithmetic operations are performed after strings are concatenated, but before comparison and logical operations

Comparison

3

Comparison operations take place after string concatenation and arithmetic operations, but before logical operations

Logical

4

Logical operators are executed after all other types of operators

4.1.2.1.1Concatenation Operator

The concatenation operator, two pipe characters known as double pipe — || — concatenates (connects together) two character strings to form a single string. Character strings can be constants or values obtained from columns or other expressions.

Example

  |SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME
  |FROM EMPLOYEE

See alsoBLOB_APPEND()

4.1.2.1.2Arithmetic Operators
Table 4.3Arithmetic Operator Precedence
OperatorPurposePrecedence

+signed_number

Unary plus

1

-signed_number

Unary minus

1

*

Multiplication

2

/

Division

2

+

Addition

3

-

Subtraction

3

Example

  |UPDATE T
  |    SET A = 4 + 1/(B-C)*D

Note

Where operators have the same precedence, they are evaluated in left-to-right sequence.

4.1.2.1.3Comparison Operators
Table 4.4Comparison Operator Precedence
OperatorPurposePrecedence

IS

Checks that the expression on the left is (not) NULL or the Boolean value on the right

1

=

Is equal to, is identical to

2

<>, !=, ~=, ^=

Is not equal to

2

>

Is greater than

2

<

Is less than

2

>=

Is greater than or equal to

2

<=

Is less than or equal to

2

!>, ~>, ^>

Is not greater than

2

!<, ~<, ^<

Is not less than

2

This group also includes comparison predicates BETWEEN, LIKE, CONTAINING, SIMILAR TO and others.

Example

  |IF (SALARY > 1400) THEN
  |

See alsoOther Comparison Predicates.

4.1.2.1.4Logical Operators
Table 4.5Logical Operator Precedence
OperatorPurposePrecedence

NOT

Negation of a search condition

1

AND

Combines two or more predicates, each of which must be true for the entire predicate to be true

2

OR

Combines two or more predicates, of which at least one predicate must be true for the entire predicate to be true

3

Example

  |IF (A < B OR (A > C AND A > D) AND NOT (C = D)) THEN …

4.1.2.2NEXT VALUE FOR

Available inDSQL, PSQL

Result typeBIGINT — dialect 2 and 3 INTEGER — dialect 1

Syntax

  |NEXT VALUE FOR sequence-name

NEXT VALUE FOR returns the next value of a sequence. SEQUENCE is the SQL-standard term for what is historically called a generator in Firebird and its ancestor, InterBase. The NEXT VALUE FOR operator is equivalent to the legacy GEN_ID (…​, increment) function with increment the increment stored in the metadata of the sequence. It is the recommended syntax for retrieving the next sequence value.

Note

Unlike the GEN_ID function, the NEXT VALUE FOR expression does not take any parameters and thus, provides no way to retrieve the current value of a sequence, nor to step the next value by a different value than the increment configured for the sequence. GEN_ID (…​, <step value>) is still needed for these tasks. A step value of 0 returns the current sequence value.

The increment of a sequence can be configured with the INCREMENT clause of CREATE SEQUENCE or ALTER SEQUENCE.

In dialect 1, the result type is INTEGER, in dialect 2 and 3 it is BIGINT.

Example

  |NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

See alsoSEQUENCE (GENERATOR), GEN_ID()

4.1.2.3AT Time Zone Expression

Available inDSQL, PSQL

Syntax

  |<at expr> ::= <expr> AT { TIME ZONE <time zone string> | LOCAL }

The AT expression expresses a datetime value in a different time zone, while keeping the same UTC instant.

AT translates a time/timestamp value to its corresponding value in another time zone. If LOCAL is used, the value is converted to the session time zone.

When expr is a WITHOUT TIME ZONE type, expr is first converted to a WITH TIME ZONE in the session time zone and then transformed to the specified time zone.

Examples

  |select time '12:00 GMT' at time zone '-03:00' from rdb$database;
  |select current_timestamp at time zone 'America/Sao_Paulo' from rdb$database;
  |select timestamp '2018-01-01 12:00 GMT' at local from rdb$database;

4.1.3Conditional Expressions

A conditional expression is one that returns different values according to how a certain condition is met. It is composed by applying a conditional function construct, of which Firebird supports several. This section describes only one conditional expression construct: CASE. All other conditional expressions apply internal functions derived from CASE and are described in Conditional Functions.

4.1.3.1CASE

Available inDSQL, PSQL

The CASE construct returns a single value from a number of possible values. Two syntactic variants are supported:

  • The simple CASE, comparable to a case construct in Pascal or a switch in C

  • The searched CASE, which works like a series of if …​ else if …​ else if clauses.

4.1.3.1.1Simple CASE

Syntax

  |
  |CASE <test-expr>
  |  WHEN <expr> THEN <result>
  |  [WHEN <expr> THEN <result> ...]
  |  [ELSE <defaultresult>]
  |END
  |

When this variant is used, test-expr is compared to the first expr, second expr and so on, until a match is found, and the corresponding result is returned. If no match is found, defaultresult from the optional ELSE clause is returned. If there are no matches and no ELSE clause, NULL is returned.

The matching works identically to the = operator. That is, if test-expr is NULL, it does not match any expr, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

Example

   |SELECT
   |  NAME,
   |  AGE,
   |  CASE UPPER(SEX)
   |    WHEN 'M' THEN 'Male'
   |    WHEN 'F' THEN 'Female'
   |    ELSE 'Unknown'
   |  END GENDER,
   |RELIGION
   |    FROM PEOPLE

A short form of the simple CASE construct is the DECODE function.

4.1.3.1.2Searched CASE

Syntax

  |CASE
  |  WHEN <bool_expr> THEN <result>
  |  [WHEN <bool_expr> THEN <result> …]
  |  [ELSE <defaultresult>]
  |END

The bool_expr expression is one that gives a ternary logical result: TRUE, FALSE or NULL. The first expression to return TRUE determines the result. If no expressions return TRUE, defaultresult from the optional ELSE clause is returned as the result. If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

Example

  |CANVOTE = CASE
  |  WHEN AGE >= 18 THEN 'Yes'
  |  WHEN AGE < 18 THEN 'No'
  |  ELSE 'Unsure'
  |END

4.1.4NULL in Expressions

NULL is not a value in SQL, but a state indicating that the value of the element either is unknown or it does not exist. It is not a zero, nor a void, nor an empty string, and it does not act like any value.

When you use NULL in numeric, string or date/time expressions, the result will always be NULL. When you use NULL in logical (Boolean) expressions, the result will depend on the type of the operation and on other participating values. When you compare a value to NULL, the result will be unknown.

Important

NULL means NULL but, in Firebird, the logical result unknown is also represented by NULL.

4.1.4.1Expressions Returning NULL

Expressions in this list will always return NULL:

  |1 + 2 + 3 + NULL
  |'Home ' || 'sweet ' || NULL
  |MyField = NULL
  |MyField <> NULL
  |NULL = NULL
  |not (NULL)

If it seems difficult to understand why, remember that NULL is a state that stands for unknown.

4.1.4.2NULL in Logical Expressions

It has already been shown that NOT (NULL) results in NULL. The interaction is a bit more complicated for the logical AND and logical OR operators:

  |NULL or false  → NULL
  |NULL or true   → true
  |NULL or NULL   → NULL
  |NULL and false → false
  |NULL and true  → NULL
  |NULL and NULL  → NULL
Tip

As a basic rule-of-thumb, if applying TRUE instead of NULL produces a different result than applying FALSE, then the outcome of the original expression is unknown, or NULL.

Examples

   |(1 = NULL) or (1 <> 1)    -- returns NULL
   |(1 = NULL) or FALSE       -- returns NULL
   |(1 = NULL) or (1 = 1)     -- returns TRUE
   |(1 = NULL) or TRUE        -- returns TRUE
   |(1 = NULL) or (1 = NULL)  -- returns NULL
   |(1 = NULL) or UNKNOWN     -- returns NULL
   |(1 = NULL) and (1 <> 1)   -- returns FALSE
   |(1 = NULL) and FALSE      -- returns FALSE
   |(1 = NULL) and (1 = 1)    -- returns NULL
   |(1 = NULL) and TRUE       -- returns NULL
   |(1 = NULL) and (1 = NULL) -- returns NULL
   |(1 = NULL) and UNKNOWN    -- returns NULL

4.1.5Subqueries

A subquery is a special form of expression that is actually a query embedded within another query. Subqueries are written in the same way as regular SELECT queries, but they must be enclosed in parentheses. Subquery expressions can be used in the following ways:

  • To specify an output column in the SELECT list

  • To obtain values or conditions for search predicates (the WHERE, HAVING clauses).

  • To produce a set that the enclosing query can select from, as though were a regular table or view. Subqueries like this appear in the FROM clause (derived tables) or in a Common Table Expression (CTE)

4.1.5.1Correlated Subqueries

A subquery can be correlated. A query is correlated when the subquery and the main query are interdependent. To process each record in the subquery, it is necessary to fetch a record in the main query; i.e. the subquery fully depends on the main query.

Sample Correlated Subquery

  |SELECT *
  |FROM Customers C
  |WHERE EXISTS
  |  (SELECT *
  |   FROM Orders O
  |   WHERE C.cnum = O.cnum
  |     AND O.adate = DATE '10.03.1990');

When subqueries are used to get the values of the output column in the SELECT list, a subquery must return a scalar result (see below).

4.1.5.2Scalar Results

Subqueries used in search predicates, other than existential and quantified predicates, must return a scalar result; that is, not more than one column from not more than one matching row or aggregation. If the result would return more, a run-time error will occur (Multiple rows in a singleton select…​).

Note

Although it is reporting a genuine error, the message can be slightly misleading. A singleton SELECT is a query that must not be capable of returning more than one row. However, singleton and scalar are not synonymous: not all singleton SELECTS are required to be scalar; and single-column selects can return multiple rows for existential and quantified predicates.

Subquery Examples
  1. A subquery as the output column in a SELECT list:

       |SELECT
       |  e.first_name,
       |  e.last_name,
       |  (SELECT
       |       sh.new_salary
       |   FROM
       |       salary_history sh
       |   WHERE
       |       sh.emp_no = e.emp_no
       |   ORDER BY sh.change_date DESC ROWS 1) AS last_salary
       |FROM
       |  employee e
    
  2. A subquery in the WHERE clause for obtaining the employee’s maximum salary and filtering by it:

       |SELECT
       |  e.first_name,
       |  e.last_name,
       |  e.salary
       |FROM employee e
       |WHERE
       |  e.salary = (
       |    SELECT MAX(ie.salary)
       |    FROM employee ie
       |  )