Operators

Operators are reserved characters that perform an operation on one or more expressions. Use them for specifying conditions or conjunctions in SQL statements.

Raijin has the following types of operators:

Arithmetic operators

This table contains the list of arithmetic operators supported by Raijin.

Table 1. List of arithmetic operators
Operator Name Operand type Return type

+

Add

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

-

Subtract

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

*

Multiply

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

/

Divide

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

%

Modulus

INT, BIGINT

INT, BIGINT

-

Negate

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

Bitwise operators

The following table lists the bitwise operators supported by Raijin.

Operator Name Operand type Return type

&

Binary AND

BOOLEAN

BOOLEAN

\

Binary OR#

INT, BIGINT#

INT, BIGINT#

^

Binary XOR#

INT, BIGINT#

INT, BIGINT#

~

Binary NOT

BOOLEAN

INT, BIGINT

<<

Binary shift left

INT, BIGINT

INT, BIGINT

>>

Binary shift right#

INT, BIGINT#

INT, BIGINT#

Comparison operators

The following table lists the comparison operators supported by Raijin.

Operator Name Operand type Return type

=

Equal

Any

BOOLEAN

!=

Not equal

Any

BOOLEAN

<>

Not equal

Any

BOOLEAN

<

Less than

Any

BOOLEAN

<=

Less than or equal

Any

BOOLEAN

>

Greater than

Any

BOOLEAN

>=

Greater than or equal

Any

BOOLEAN

Logical operators

The following table lists the logical operators supported by Raijin.

Operator Name Operand type Return type

AND

Logical AND

BOOLEAN

BOOLEAN

OR

Logical OR

BOOLEAN

BOOLEAN

XOR

Logical XOR#

BOOLEAN#

BOOLEAN#

NOT

Logical NOT

BOOLEAN

BOOLEAN

~~

Same as LIKE

STRING

STRING

!~~

Same as NOT LIKE

STRING

STRING

~~*

Same as ILIKE

STRING

STRING

!~~*

Same as NOT ILIKE

STRING

STRING

LIKE

Pattern-based search, case-sensitive

STRING

BOOLEAN

ILIKE

Pattern-based search, case-insensitive

STRING

BOOLEAN

IN

Search in a list of values

Any

BOOLEAN

LIKE

The LIKE operator is used in a WHERE clause to search for a pattern in a column. The LIKE operator is case-sensitive.

Two wildcards are supported, % represents zero or more characters, while _ represents exactly one character.

Syntax
LIKE 'expr'
Parameters
expr

Search pattern to match.

ILIKE

The ILIKE operator is used in a WHERE clause to search for a pattern in a column. The ILIKE operator is not case-sensitive.

Two wildcards are supported, % represents zero or more characters, while _ represents exactly one character.

Syntax
ILIKE 'expr'
Parameters
expr

Search pattern to match.

IN

The IN operator is used to match an expression’s result against a list of values. It is a shorthand for multiple OR conditions.

Syntax
expr IN (value_1, value_2, ..., value_n)

or

expr IN (subquery)
Parameters
expr

Value to match.

value_1, value_2, …​, value_n

List of values to match against. The IN operator returns true if any value in the list matches the expression.

subquery

A SELECT statement returning exactly one column. The IN operator returns true if any value in the result set produced by the subquery matches the expression.

Special care should be taken when multiple IN operators joined by an AND specify different value types to match against. This is because when the value lists contain variant data, the values are internally converted to a common data type for evaluation, depending on the value types specified. Therefore, the result might be different from what you expect.

Example 1. Multiple IN operators with variant data
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl();
INSERT INTO tbl(a) VALUES (-5);

SELECT * FROM tbl WHERE a IN (-5) AND a IN ('-5', '-2');

-- Returns an empty result even though you might expect -5 to be found

Set operators

Raijin supports the following set operators:

UNION

The UNION operator is used to combine the results of multiple SELECT statements. It requires that:

  • All SELECT statements have an equal number of columns

  • The columns in each SELECT statement have similar data types

  • The columns in each SELECT statement are specified in the same order

Syntax
SELECT col_1, col_2, ..., col_n FROM table1
UNION
SELECT col_1, col_2, ..., col_n FROM table2
Parameters
col_1, col_2, …​, col_n

The list of columns to retrieve.

table

Table from where to retrieve the records.

Example 2. Using UNION
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl1(a INTEGER, b INTEGER);
INSERT INTO tbl1(a, b) VALUES
    (1, 10),
    (2, 20),
    (3, 30);

CREATE TABLE tbl2(a INTEGER, b INTEGER);
INSERT INTO tbl2(a, b) VALUES
    (3, 30),
    (4, 40),
    (5, 50);

SELECT a, b FROM tbl1 UNION SELECT a, b FROM tbl2;
SELECT a, b, (a+b) AS sum FROM tbl1 UNION SELECT a, b, (a+b) FROM tbl2;
Result
{"a":1,"b":10}
{"a":2,"b":20}
{"a":3,"b":30}
{"a":4,"b":40}
{"a":5,"b":50}
{}
{"a":1,"b":10,"sum":11}
{"a":2,"b":20,"sum":22}
{"a":3,"b":30,"sum":33}
{"a":4,"b":40,"sum":44}
{"a":5,"b":50,"sum":55}

UNION ALL

The UNION ALL operator combines the results of multiple SELECT statements, including duplicate rows.

Syntax
SELECT col_1, col_2, ..., col_n FROM table1
UNION ALL
SELECT col_1, col_2, ..., col_n FROM table2
Usage notes

See the UNION operator description.

Example 3. Using UNION ALL
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl1(a INTEGER, b INTEGER);
INSERT INTO tbl1(a, b) VALUES
    (1, 10),
    (2, 20),
    (3, 30);

CREATE TABLE tbl2(a INTEGER, b INTEGER);
INSERT INTO tbl2(a, b) VALUES
    (3, 30),
    (4, 40),
    (5, 50);

SELECT a, b FROM tbl1 UNION ALL SELECT a, b FROM tbl2;
SELECT a, b, (a+b) AS sum FROM tbl1 UNION ALL SELECT a, b, (a+b) FROM tbl2;
Result
{"a":1,"b":10}
{"a":2,"b":20}
{"a":3,"b":30}
{"a":3,"b":30}
{"a":4,"b":40}
{"a":5,"b":50}
{}
{"a":1,"b":10,"sum":11}
{"a":2,"b":20,"sum":22}
{"a":3,"b":30,"sum":33}
{"a":3,"b":30,"sum":33}
{"a":4,"b":40,"sum":44}
{"a":5,"b":50,"sum":55}

String operators

The following table lists the string operators supported by Raijin.

Operator Name Operand type Return type

|

Concatenate

Any

STRING

MATCH AGAINST

Word-based search, case-sensitive

STRING

BOOLEAN

IMATCH AGAINST

Word-based search, case-insensitive

STRING

BOOLEAN

MATCH AGAINST

Performs word-based search in selected columns, case-sensitive.

Syntax
MATCH (col_name [, ...]) AGAINST (expr [, ...])
Parameters
col_name

Column to be searched.

expr

Search string that specifies which words to search for.

Usage notes

The table below shows some example searches. Words in a search string not preceded by operator are optional, and the row is selected if any of the optional words is present in a column. Words preceded by + are mandatory, and they must be present in a row. Words preceded by - are prohibited, and they must not be present in a row.

Note that - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a search that contains only terms preceded by - returns an empty result. It does not return "all rows except those containing any of the excluded terms."

For + and - operators to have effect, they must be placed immediately before the word or open parentheses. Otherwise, they are ignored. For example, + word1 or - word1 are interpreted as ` word1` (meaning that word1 is optional). Similarly, +word1 - (word2 word3) is interpreted as +word1 (word2 word3).

Table 2. MATCH AGAINST queries
Query Description

SELECT * FROM tbl WHERE MATCH (*) AGAINST ('word1')

Selects all rows that contain word1 in any column

SELECT * FROM tbl WHERE MATCH (col1, col2) AGAINST ('word1')

Select all rows that contain word1 in col1 and col2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1')

Selects all rows that contain word1

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 word2')

Selects all rows that contain either word1 or word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('+word1 +word2')

Selects all rows that contain both word1 and word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 -word2')

Selects all rows that contain word1 and do not contain word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 word2 +(word3 word4)')

Selects all rows that contain word1 or word2, and which either contain word3 or word4

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('+word1 -(word2 word3)')

Selects all rows that contain word1 and that neither contain word2 nor word3

Column is tokenized or split to words by considering everything that is not a letter or digit as a word boundary. That is, word is a sequence of letters and/or digits.

Infix syntax with the operators explicitly named and placed between operands is not supported, but it can be expressed using the supported syntax:

word1 OR word2

word1 word2

word1 AND word2

+word1 +word2

word1 AND NOT word2

+word1 -word2

IMATCH AGAINST

Performs word-based search, case-insensitive.

Syntax
IMATCH (col_name) AGAINST (expr)
Parameters
col_name

Column to be searched.

expr

Search string that specifies which words to search for.

Usage notes

See the MATCH AGAINST operator description.