Binary Expressions

Binary operators take two values as parameter expressions and combine or compare them in some way that produces an output value. This section includes all of the operators with nonkeyword representations, plus AND and OR. With the exception of AND and OR, all of these operators will produce a NULL result if either parameter expression is NULL.

SQLite supports the following binary expression operators:

|| String concatenation

The || operator is used to concatenate text values. This operator is defined by the SQL standard. Although some databases support the nonstandard + operator for concatenating text values, SQLite does not. Non-NULL parameter expressions will first be converted to text values.

* Multiplication

Standard numeric multiplication of two numbers. Non-NULL parameter expressions will first be converted to numeric values. If both expressions are integers, the result will also be an integer.

/ Division

Standard numeric division of two numbers. The result will be the lefthand operator divided by the righthand operator. Non-NULL parameter expressions will first be converted to numeric values. If both expressions are integers, the integer division will be used and the result will also be an integer.

% Modulo or remainder

Standard numeric modulo of two numbers. The expression value will be the remainder of the left operator divided by the right. If either parameter expression is a real number, the result will be a real number. The result will be a whole number between 0 and one less than the value of the converted righthand expression. Non-NULL parameter expressions will first be converted to numeric values.

+ Addition

Standard numeric addition of two numbers. If both parameter expressions are integers, the result will also be an integer. Non-NULL parameter expressions will first be converted to numeric values.

- Subtraction

Standard numeric subtraction of two numbers. If both parameter expressions are integers, the result will also be an integer. Non-NULL parameter expressions will first be converted to numeric values.

<< >> Bit shifts

Binary bit shift. The lefthand expression is shifted right (>>) or left (<<) by the number of bits indicated in the right operator. Any non-NULL parameter expressions will first be converted into integers. These operators should be familiar to C programmers, but are nonstandard operators in SQL.

& | Binary AND, OR

Binary AND and OR bitwise operations. Any non-NULL parameter expression will first be converted into integers. Logic expressions should not use these operators, but should use AND or OR instead. As with bit shifts, these are nonstandard operators.

< <= => > Greater-than, less-than variations

Compares the parameter expressions and returns a logic value of 0, 1, or NULL, depending on which expression is greater-than, less-than, or equal-to. Parameter expressions do not need to be numeric, and will not be converted. In fact, they don’t even need to be the same type. The results may depend on the collations associated with the parameter expressions.

= == Equal

Compares the operands for equality and returns a logic value of 0, 1, or NULL. Like most logic operators, equal is bound by the rules of three valued logic. In specific, NULL == NULL will result in a NULL, not true (1). The specific definition of equal for text values depends on the collations associated with the parameter expressions. Both forms (single or double equal sign) are the exact same.

!= <> Not equal

Compares the expressions for inequality and returns a logic value of 0, 1, or NULL. Like equal, not equal is also bound by the rules of three valued logic ,so NULL != NULL is NULL, not false (0). The specific definition of not equal depends on the collations associated with the parameter expressions. Both forms are the same.

AND OR Logical AND, OR

Logical and and or operators. These can be used to string together complex logic expressions.

The AND and OR operators are some of the only operators that may return an integer logic value when one of their parameter expressions is NULL. See Three-Valued Logic for more details on how AND and OR operate under three valued logic.

These operators are listed in order of precedence. However, a number of operators have the same precedence as their neighbors. As with most expression languages, SQL allows subexpressions to be enclosed in parentheses to enforce a specific evaluation ordering.