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.

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

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

       IF (SALARY > 1400) THEN
       …
            

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

Available: DSQL, PSQL

NEXT VALUE FOR returns the next value of a sequence. SEQUENCE is an SQL-compliant term for a generator in Firebird and its ancestor, InterBase. The NEXT VALUE FOR operator is equivalent to the legacy GEN_ID (..., 1) function and is the recommended syntax for retrieving the next sequence value.

Syntax for NEXT VALUE FOR: 

       NEXT VALUE FOR sequence-name
          

Example: 

       NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;
          

	Note
	Unlike GEN_ID (..., 1), the NEXT VALUE FOR variant 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 more than 1. GEN_ID (..., <step value>) is still needed for these tasks. A <step value> of 0 returns the current sequence value.

See also:  SEQUENCE (GENERATOR), GEN_ID()

Available:  DSQL, PSQL

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

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

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

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

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

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”.

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
          

Up to and including Firebird 2.5.x, there is no implementation for a logical (Boolean) data type—that is coming in Firebird 3. However, there are logical expressions (predicates) that can return true, false or unknown.

Examples: 

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

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.

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: