# SQL Operators - Firebird

Firebird SQL syntax includes operators for comparing and evaluating columns, constants, variables, and embedded expressions to produce distinct assertions.

Precedence of Operators

Precedence determines the order in which operators and the values they affect are evaluated in an expression.

When an expression contains several operators of the same type, those operators are evaluated from left to right unless there is a conflict where two operators of the same type affect the same values. When there is a conflict, operator precedence within the type determines the order of evaluation. Table lists the precedence of Firebird operator types, from highest to lowest.

Operator Type Precedence

Concatenation Operator

The concatenation operator || combines two character strings to produce a single string. Character strings can be constants or values retrieved from a column:

SELECT Last_name ||', ' || First_Name AS Full_Name FROM Membership;

Firebird is more particular about string overflow than its forebears. It will check the lengths of input columns and block concatenation if the aggregate width would potentially exceed the limit for a VARCHAR (32,765 bytes).

Arithmetic Operators

Arithmetic expressions are evaluated from left to right, except when ambiguities arise. In these cases, arithmetic operations are evaluated according to the precedence specified in Table. For example, multiplications are performed before divisions, and divisions are performed before subtractions.

Arithmetic operations are always performed before comparison and logical operations. To change or force the order of evaluation, group operations in parentheses.

Arithmetic Operator Precedence

The following example illustrates how a complex calculation would be nested to ensure that the expression would be computed in the correct, unambiguous order:

... SET COLUMN_A = 1/((COLUMN_B * COLUMN_C/4) - ((COLUMN_D / 10) + 28))

The engine will detect syntax errors—such as unbalanced parentheses and embedded expressions that do not produce a result —but it cannot detect logic errors or ambiguities that are correct syntactically. Very complicated nested calculations should be worked out on paper. To simplify the process of composing these predicates, always start by isolating the innermost operation and working “toward the outside,” testing predicates with a calculator at each step.

Comparison Operators

Comparison operators test a specific relationship between a value to the left of the operator, and a value or range of values to the right of the operator. Every test predicates a result that can be either true or false. Rules of precedence apply to evaluation, as indicated in Table.

Comparisons that encounter NULL on either the left or right side of the operator always follow SQL rules of logic and evaluate the result of the comparison as NULL and return false, for example:

IF (YEAR_OF_BIRTH < 1950)

This returns false if YEAR_OF_BIRTH is 1950 or later or is NULL.

For more discussion of NULL, refer to the upcoming section “Considering NULL.”

Value pairs compared can be columns, constants, or calculated expressions, and they must resolve to the same data type. The CAST( ) function can be used to translate a value to a compatible data type for comparison.

Comparison Operator Precedence

Arithmetical predicates using these symbols need no explanation. However, string tests can use them as well. Tests using the = and <> comparisons are clear enough. The following statement uses the >= operator to return all rows where LAST_NAME is equal to the test string and also where LAST_NAME evaluates as alphanumerically “greater”:

SELECT * FROM EMPLOYEE WHERE LAST_NAME >= 'Smith';

There is no 'Smith' in Employee, but the query returns 'Stansbury', 'Steadman', and so on, right through to 'Young'. The Employee database in the example uses default character set NONE, in which lowercase characters take precedence over uppercase characters. In our example, 'SMITH' would not be selected because the search string 'SM' (83 + 77) evaluates as less than 'Sm' (83 + 109).

Character Sets and Collations

Alphanumeric sequence is determined at two levels: character set and collation sequence. Each character set has its own, unique rules of precedence and, when an alternative collation sequence is in use, the rules of the collation sequence override those of the character set.

The default collation sequence for any character set—that is, the one whose name matches that of the character set—is binary. The binary collation sequence is determined to ascend according to the numeric code of the character in the system in which it is encoded (ASCII, ANSI, Unicode, etc.). Alternative collation sequences typically override the default order to comply with the rules of the locale or with special rules, such as case insensitivity or dictionary orderings.

Other Comparison Predicators

The predicators BETWEEN, CONTAINING, LIKE, and STARTING WITH, summarized in the following table, have equal precedence in evaluations, behind the comparison operators listed in the previous table. When they conflict with one another they are evaluated strictly from left to right.

Other Comparison Predicators

This is most efficient when the set is a small group of constants. The set cannot exceed 1,500 members. See also the upcoming section “Existential Predicates.” BETWEEN takes two arguments of compatible data types separated by the keyword AND. The lower value must be the first argument. If not, the query will find no rows matching the predicate. The search is inclusive—that is, values matching both arguments will be included in the returned set, for example:

SELECT * FROM EMPLOYEE WHERE HIRE_DATE BETWEEN '01.01.1992' AND CURRENT_TIMESTAMP;

BETWEEN will use an ascending index on the searched column if there is one available.

CONTAINING searches a string or string-like type looking for a sequence of characters that matches its argument. By some internal magic, it can be used for an alphanumeric (string-like) search on numbers and dates. A CONTAINING searc h is case insensitive.

The following example

SELECT * FROM PROJECT WHERE PROJ_NAME CONTAINING 'Map'; returns two rows for the projects 'AutoMap' and 'MapBrowser port'. This example
SELECT * FROM SALARY_HISTORY WHERE CHANGE_DATE CONTAINING 93;

returns all rows where the date is in the year 1993.

In some releases, CONTAINING is unpredictable when used with BLOBs of more than 1024 bytes. It was recognized as a bug and fixed soon after the v.1.5 release.

IN takes a list of values as an argument and returns all rows where the column value on the left side matches any valuein the set, for example:

SELECT * FROM EMPLOYEE WHERE FIRST_NAME IN('Pete', 'Ann', 'Roger');

NULL cannot be included in the search set.

IN( ) can be made to form its own argument list from a subquery that returns single-column rows. For details, see the section “Existential Predicates” later in this chapter.

It is a common “newbie” mistake to treat the predicate “IN (<value>)” as if it were equivalent to “= <value>” because the two are logically equivalent, insofar as both return the same result. However, IN ( ) does not use an index. Since equality searches are usually better optimized by an index, usage of IN ( ) as a substitute is a poor choice from the perspective of performance.

LIKE is a case-sensitive pattern search operator. It is quite a blunt instrument compared to pattern searches in regular expression engines. It recognizes two “wild- card” symbols, % and _ (the underscore character), that work as follows:

• % can be substituted into a search string to represent any number of unknown characters, including none, for example:

LIKE '%mit%'

will be true with strings such as 'blacksmith', 'mitgenommen', 'commit', as well as 'mit'.

• An underscore character (_) can be substituted into a search string to represent a single, unknown character. For example, the following searches for records where the surname might be 'Smith', 'Smyth', or a similar pattern:

LIKE 'Sm_th'

If you need to search for a string that contains one or both of the wildcard characters in the literal part of the pattern, you can “escape” the literal character —that is, mark it as a literal character by prepending a special escape character. To make this work, you need to tell the engine to recognize your escape character.

For example, say you want to use the LIKE operator to select all of the names of the system tables. Assume that all system table identifiers have at least one underscore character—well, it is almost true! You decide to use '#' as your escape character. Here is how you would do it:

SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB\$RELATION_NAME LIKE '%#_%' ESCAPE '#';

LIKE searches, per se, do not use an index. However, a predicate using LIKE 'Your string%' will be resolved to a STARTING WITH predicate, which does use an index, if one is available.

STARTING WITH predicates a case-sensitive search for a string that starts with the supplied string. It follows the byte-matching rules of the prevailing character set and collation. If necessary, it can take a COLLATION argument to force the search to use a specific collation sequence.

The following example:

SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE WHERE LAST_NAME STARTING WITH 'Jo';

would return rows where the last name was 'Johnson', 'Jones', 'Joabim', etc.

In a database storing character columns in German, where the default character set was ISO8859_1, you might want to force the collation sequence, as follows:

SELECT FAM_NAME, ERSTE_NAME FROM ARBEITNEHMER WHERE COLLATE DE_DE FAM_NAME STARTING WITH 'Schmi';

STARTING WITH will use an index if one is available.

Logical Operators

Firebird provides three logical operators that can operate on or with other predicates in distinct ways:

• NOT predicates the negative of the search condition to which it is applied. It has the highest precedence.
• AND forms a complex predicate by combining two or more predicates, all of which must be true for the entire predicate to be true. It is next in precedence.
• OR forms a complex predicate by combining two or more predicates, of which one must be true for the entire predicate to be true. OR is last in precedence. By default, Firebird 1.5 uses “shortcut” Boolean logic to determine the outcome of an ORed predicate. As soon as one embedded predicate returns as true, evaluation stops and the entire predicate returns as true.

This is an example of a logical predicate:

COLUMN_1 = COLUMN_2 OR ((COLUMN_1 > COLUMN_2 AND COLUMN_3 < COLUMN_1) AND NOT (COLUMN_4 = CURRENT_DATE))

The effect of this predicate will be as follows:

If COLUMN _1 and COLUMN_2 are equal, evaluation stops and the predicate returns true.

Otherwise, the next predicate —with two embedded predicates—is tested to see whether it could be true. The first embedded predicate itself has two predicates embedded, both of which must be true to make it true as a whole. If COLUMN _1 is less than COLUMN_2, evaluation stops and the predicate as a whole returns false.

If COLUMN_1 is greater, then the COLUMN_3 is evaluated against COLUMN_1.

If COLUMN_3 is equal to or greater than COLUMN_1, evaluations stops and the predicate as a whole returns false.

Otherwise, evaluation proceeds to the final embedded predicate. If COLUMN_4 is equal to the current date, the predicate as a whole returns false; if not, it returns true.

Inclusive vs. Exclusive OR

Firebird’s OR is the inclusive OR (any single condition or all conditions can be true). The exclusive OR (which evaluates to true if any single condition is true but false if all conditions are true) is not supported.

The IS [NOT] NULL Predicate

IS NULL and its alter ego IS NOT NULL are a pair of predicators that defy grouping. Because NULL is not a value, they are not comparison operators. They test the assertion that the value to the left has a value (IS NOT NULL) or has no value (IS NULL).

If there is a value, IS NOT NULL returns true, regardless of the content, size, or data type of the value.

Newcomers to SQL sometimes confuse the NOT NULL constraint with the IS [NOT] NULL predicates. It is a common mistake to presume that IS NULL and IS NOT NULL test whether a column has been defined with the NOT NULL constraint. It is not true. They test the current contents of a column to determine the presence or absence of a value.

An IS [NOT] NULL predicate can be used to test input data destined for posting to database columns that are constrained as NOT NULL. It is very common to use this predicate in Before Insert triggers.

Existential Predicates

The last group of predicates comprises those that use subqueries to supply values for assertions of various kinds in search conditions. The predicating keywords are ALL, ANY, EXISTS, IN, SINGULAR, and SOME. They came to be referred to as existential because all play roles in search predicates that test in some way for the existence of the left-side value in the output of embedded queries on other tables.

All of these predicators —summarized in Table—are involved in some way with subqueries. The topic of subqueries gets detailed treatment in the next chapter.

Existential Predicators

The EXISTS( ) Predicate

By far the most useful of all of the existential predicators, EXISTS provides the fastest possible method to test for the existence of a value in another table.

Often, in stored procedures or queries, you want to know whether there are any rows in a table meeting a certain set of criteria. You are not interested in how many such rows exist—you only want to determine whether there is at least one. The strategy of performing a COUNT(*) on the set and evaluating any returned value greater than 0 as “true” is costly in Firebird.

A row count performed in the context of one transaction to establish a condition for proceeding with work in another—for example, to calculate the value for a “next” key—is completely unreliable.

The standard SQL predicate EXISTS(subqueried value) and its negative counterpart NOT EXISTS provide a way to perform the set-existence test very cheaply, from the point of view of resources used. It does not generate an output set but merely courses through the table until it meets a row complying with the conditions predicated in the subquery. At that point, it exits and returns true. If it finds no match in any row, it returns false.

In the first example, the EXISTS( ) test predicates the conditions for performing an update using a dynamic SQL statement:

UPDATE TABLEA SET COL6 ='SOLD' WHERE COL1 = 99 AND EXISTS(SELECT COLB FROM TABLEB WHERE COLB = 99);

A statement like the example would typically take replaceable parameters on the right side of the predicate expressions in the WHERE clause.

In reality, many subqueries in EXISTS( ) predicates are correlated —that is, the search conditions for the subquery are relationally linked to one or more columns in the main query. Taking the preceding example and replacing the hard-coded constant in the search condition with a column reference, a more likely query would be

UPDATE TABLEA SET TABLEA.COL6 ='SOLD' WHERE EXISTS(SELECT TABLEB.COLB FROM TABLEB WHERE TABLEB.COLB = TABLEA.COL1);

The effect of the existential expression is to set the condition “If there is at least one matching row in TABLEB, then perform the update.”

The IN( ) Predicate

The IN( ) predicate, when used with a subquery, is similar to EXISTS( ) insofar as it can predicate on the output of a subquery, for example:

UPDATE TABLEA SET COL6 ='SOLD' WHERE COL1 IN (SELECT COLB FROM TABLEB WHERE COLJ > 0);

In this case, the subquery returns a set of all values for COLB in the second table that comply with its own WHERE clause. The IN( ) predicate causes COL1 to be compared with each returned value in the set, until a match is found. It will perform the update on every row of TABLEA whose COL1 value matches any value in the set.

In Firebird, an IN( ) predicate that specifies a subqueried set is actually resolved to an EXISTS( ) predicate for the comparison operation.

Limitations

From a performance point of view, IN( ) is not useful where the subquery is likely to produce more than a few values. It is of most use in forming a set of comparison values from a small lookup table or from a fixed set of constants, for example:

UPDATE TABLEA SET COL6 ='SOLD' WHERE COL1 IN ('A', 'B', 'C', 'D');

The number of constant values that can be supplied for a set is limited to a maximum of 1,500 values—possibly less, if the values are large and the size of the query string would exceed its 64K limit.

The ALL( ) Predicate

Usage is best illustrated by starting with an example:

SELECT * FROM MEMBERSHIP WHERE (EXTRACT (YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM DATE_OF_BIRTH))< ALL (SELECT MINIMUM_AGE FROM AGE_GROUP);

The left-side expression calculates the age in years of each member in the Membership table and outputs only those members who are younger than the minimum age in the Age_Group table. ALL( ) is of limited use, since it is really only appropriate for predicating this sort of greater-than or less-than search to test for exclusion.

The SINGULAR( ) Predicate

SINGULAR is similar to ALL( ), except that it predicates that one and only one matching value be found in the embedded set. For example, this query is looking for all orders that have only a single order-detail line:

SELECT OH.ORDER_ID FROM ORDER_HEADER OH WHERE OH.ORDER_ID = SINGULAR (SELECT OD.ORDER_ID FROM ORDER_DETAIL OD);

ANY( ) and SOME( ) Predicates

These two predicators are identical in behavior. Apparently, both are present in the SQL standard for interchangeable use to improve the readability of statements. For equality comparisons, they are logically equivalent to the EXISTS( ) predicate. However, because they can predicate on other comparisons, such as >, <, >=, <=, STARTING WITH, LIKE, and CONTAINING, they are especially useful for existential tests that EXISTS( ) cannot perform.

The following statement will retrieve a list of all employees who had at least one salary review within a year of being hired:

SELECT E.EMP_NO, E.FULL_NAME, E.HIRE_DATE FROM EMPLOYEE E WHERE E.HIRE_DATE + 365 > SOME ( SELECT SH.CHANGE_DATE FROM SALARY_HISTORY SH WHERE SH.EMP_NO = E.EMP_NO);

Table Aliases

Notice the use of table aliases in the last two examples to eliminate ambiguity between matching column names in two tables. Firebird is very particular about aliasing tables in multi-table queries.