SQL operator is a series of functions used for comparison, which are widely used in WHERE clauses of SELECT statement.

Arithmetic Operators

Arithmetic operators usually appear in expressions consisting of left operands, operators, and (in most cases) right operands.

• + and -: can be used as unary or binary operator. When it is used as a unary operator, such as +1, 2.5 or col_name, it means that the value is multiplied by +1 or 1. Therefore, the unary operator + returns the unchanged value, while the unary operator changes the symbolic bit of the value. The user can superimpose two unary operators, such as ++5 which returns a positive value, +2 or +2 which returns a negative value. However, the user cannot use because is interpreted as the remark statement. However, can be used only when a space or parenthesis is inserted between them, such as (2) or 2, which means that the actual expression result is +2. When + or is used as a binary operator such as 2+2, 3+1.5 or col1 + col2, the expression means adding or subtracting the right value from the left value. The left and right values must be numeric type.
• * and /: represent multiplication and division, respectively. Operands on both sides of the operator must be data type. When two numerals are multiplied, operands of smaller type may be promoted, e.g., SMALLINT is promoted to INT or BIGINT, etc. If necessary, the result of expression is promoted to the next larger type, e.g., the type of result generated by TINYINT multiplied by INT is BIGINT. When two numerals are multiplied, the operand and the expression result are both interpreted as DOUBLE type to avoid loss of precision. To convert the expression result to another type, the user needs to convert it with CAST function.
• %: modulus operator. Returns the remainder of the left operand divided by the right operand. Both the left and right operands must be integer type.
• &, | and ^: The bitwise operator returns the bitwise-AND, bitwise-OR, and bitwise-XOR operation results for two operands. Both operands are required to be an integral type. If the types of the two operands of the bitwise operator are different, the smaller operand is increased to the bigger operand, and then the corresponding bitwise operation is performed. Multiple arithmetic operators may appear in one expression. The user can use parentheses to enclose the corresponding arithmetic expression. Typically, the arithmetic operator does not have corresponding mathematical functions to express the same feature as the arithmetic operator. For example, we don't use the MOD() function to express the feature of the % operator. On the contrary, the mathematical function does not have a corresponding arithmetic operator. For example, the power function POW() does not have a corresponding ** exponentiation operator. Users can learn about which arithmetic functions we support through the chapter on mathematical functions.

Between Operator

In WHERE clauses, expressions may be compared with upper and lower bounds at the same time. If the expression is greater than or equal to the lower bound and less than or equal to the upper bound, the comparison result is TRUE.

Syntax:

expression BETWEEN lower_bound AND upper_bound

Data type: Usually, the calculation result of expression is numeric type, and this operator also supports other data types. If you must ensure that the lower and upper bounds are comparable characters, you can use the cast () function.

Instructions for use: Be careful when you use string operands. Long strings starting with the upper bound do not match the upper bound, which is larger than the upper bound. Between 'A' and 'M' cannot match 'mJ'. To ensure that the expression works, some functions can be used, such as upper(), lower(), substr(), and trim ().

Example:

mysql> select c1 from t1 where month between 1 and 6;

Comparison Operators

The comparison operator is used to determine whether a column is equal to other or to sort columns. =, !=, <>, <, <=, >, and >= can be applied for all data types. Among them, <> means not equal to, which is the same as !=. Operators IN and BETWEEN provide a shorter expression to describe the comparison of such relationships as equal, less than, size, etc.

In Operator

The In operator is compared with the VALUE set and returns TRUE if it can match any element in the set. The parameter and VALUE set must be comparable. All expressions using the operator IN can be written as equivalent comparison connected by OR. But the syntax of the operator IN is simpler, more accurate, and easier than OR for Doris to optimize.

For example:

mysql> select * from small_table where tiny_column in (1,2);

Like Operator

This operator is used to compare strings. _ is used to match a single character, and % is used to match multiple characters. Parameters must match a complete string. In general, putting % at the end of a string is more practical.

Example:

 mysql> select varchar_column from small_table where varchar_column like 'm%';
 +----------------+
 | varchar_column |
 +----------------+
 | milan          |
 +----------------+
 1 row in set (0.02 sec)

 mysql> select varchar_column from small_table where varchar_column like 'm____';
 +----------------+
 | varchar_column |
 +----------------+
 | milan          |
 +----------------+
 1 row in set (0.01 sec)

Logical Operators

The logical operators return a BOOL value. The logical operators include unary operator and plurality operator. The parameters processed by each operator are expressions that return a BOOL value. Supported operators are:

• AND: This is a binary operator, which returns TRUE if the calculation result of the left and right parameters are TRUE.
• OR: This is a binary operator, which returns TRUE if the calculation result for either of the left and right parameters is TRUE. If both parameters are FALSE, the OR operator returns FALSE.
• NOT: This is a unary operator that inverts the result of the expression. If the parameter is TRUE, the operator returns FALSE. If the parameter is FALSE, the operator returns TRUE.

Example:

 mysql> select true and true;
 +-------------------+
 | (TRUE) AND (TRUE) |
 +-------------------+
 |                 1 |
 +-------------------+
 1 row in set (0.00 sec)

 mysql> select true and false;
 +--------------------+
 | (TRUE) AND (FALSE) |
 +--------------------+
 |                  0 |
 +--------------------+
 1 row in set (0.01 sec)

 mysql> select true or false;
 +-------------------+
 | (TRUE) OR (FALSE) |
 +-------------------+
 |                 1 |
 +-------------------+
 1 row in set (0.01 sec)

 mysql> select not true;
 +----------+
 | NOT TRUE |
 +----------+
 |        0 |
 +----------+
 1 row in set (0.01 sec)

Regular Expression Operators

Judge whether the parameters match regular expressions. You need to use regular expressions meeting POSIX standard. ^ is used to match the header of a string, $ to match the tail of a string, . to match any single character, * to match 0 or more options, + to match 1 or more options, and ? to express fractal greedy expressions, etc. Regular expressions need to match a complete value, not just part of the string. If you want to match the middle part, the front part of the regular expression can be written as . * or . *. In general, ^ and $ can be omitted. Operators RLKIE and REGEXP are synonymous. The | operator is an optional operator. The regular expressions on both sides of the | operator only need to satisfy the conditions on one side. The | operator and the regular expressions on both sides usually need to be enclosed in ().

Example:

 mysql> select varchar_column from small_table where varchar_column regexp '(mi|MI).*';
 +----------------+
 | varchar_column |
 +----------------+
 | milan          |
 +----------------+
 1 row in set (0.01 sec)

 mysql> select varchar_column from small_table where varchar_column regexp 'm.*';
 +----------------+
 | varchar_column |
 +----------------+
 | milan          |
 +----------------+
 1 row in set (0.01 sec)