# Operators¶

Operators normally use special characters or keywords to manipulate single or multiple operands (either scalar, columns, or subqueries). They are a kind of a binary type function that requires a minimum of two (or more) operands to return results.

## Order of precedence¶

The table below shows the order of precedence for the operators.

Order of precedence for operators

Order

Operators

1

parentheses

2

3

4

comparison operators,

[NOT] LIKE, [NOT] BETWEEN, [NOT] IN,

IS [NOT] NULL, IS [NOT] TRUE, IS [NOT] FALSE

5

NOT

6

AND

7

OR

In case if the operators have the same precedence they are evaluated left-to-right.

## Arithmetic operators¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE

Arithmetic operators provide the capability to perform arithmetic operations on columns or numbers.

The addition operator performs a sum of two numeric columns or numbers.

#### Examples¶

Perform addition of elements in two columns.

SELECT <col_1> + <col_2>
FROM <table_name>


It can also add a number to a column

SELECT <col_1> + <number>
FROM <table_name>


or add two or more numbers and columns together.

SELECT <col_1> + <col_2> + <number>
FROM <table_name>


### Subtraction¶

The subtraction operator subtracts number in one column from the values in another one or a number.

#### Examples¶

Subtract values in one column from another one.

SELECT <col_1> - <col_2>
FROM <table_name>


Subtract a number from a column.

SELECT <col_1> - <number>
FROM <table_name>


### Multiplication¶

The multiplication operator multiplies values between two or more columns or numbers.

#### Examples¶

Multiply values between two columns.

SELECT <col_1> * <col_2>
FROM <table_name>


Mutliply the values in one column and a number.

SELECT <col_1> * <number>
FROM <table_name>


### Division¶

The division operator takes values from one column and divides them by the values in another or a number.

Warning

If the divisor column contains 0 or NULL BlazingSQL will return NULL.

#### Examples¶

Divide values from one column by the values in another column.

SELECT <col_1> / <col_2>
FROM <table_name>


Divide values from one column by the a number.

Warning

Dividing explicitly by a literal 0 will throw a RunExecuteGraphError exception.

SELECT <col_1> * <number>
FROM <table_name>


### Negation¶

The negation operator returns a negative of a value in a column or of a number.

#### Examples¶

Negate values in a column.

SELECT -<col_1>
FROM <table_name>


## Comparison operators¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

Comparison operators compare two values and return a truth value: either TRUE or FALSE. These normally require columns or literals with comparable data types.

### <¶

Operator to check if values in one column are less than the values in another, or a literal.

#### Example¶

SELECT <col_1> < <col_2>
FROM <table_name>


### <=¶

Operator to check if values in one column are less than or equal to the values in another, or a literal.

#### Example¶

SELECT <col_1> <= <col_2>
FROM <table_name>


### >¶

Operator to check if values in one column are more than the values in another, or a literal.

#### Example¶

SELECT <col_1> > <col_2>
FROM <table_name>


### >=¶

Operator to check if values in one column are more than or equal to the values in another, or a literal.

#### Example¶

SELECT <col_1> >= <col_2>
FROM <table_name>


### =¶

Operator to check if values in one column are equal to the values in another, or a literal.

#### Example¶

SELECT <col_1> = <col_2>
FROM <table_name>


### <>¶

Operator to check if values in one column are not equal to the values in another, or a literal.

#### Example¶

SELECT <col_1> <> <col_2>
FROM <table_name>


### BETWEEN¶

Operator to check if values in a column lay in or outside a specified range, specified by either literals or columns.

#### Examples¶

The range can be specified by literals.

SELECT <col_1> BETWEEN <number_1> AND <number_2>
FROM <table_name>


The range can also be specified by columns.

SELECT <col_1> BETWEEN <col_2> AND <col_3>
FROM <table_name>


To check if values lay outside of the range use NOT qualifier.

SELECT <col_1> NOT BETWEEN <number_1> AND <number_2>
FROM <table_name>


### LIKE¶

Supported datatypes: VARCHAR

Operator to check if a string matches a pattern specified in the second operand. The % sign matches any number of characters while the _ matches only a single character. To match either %, _, or the \  you need to prefix the pattern with another \  thus resulting in \% to match a percent sign, \_ to match underscore, and \\ to match a backslash.

Note

The LIKE operator is case-sensitive so to match strings with different casing all strings need to be normalized. See LOWER, UPPER or INITCAP.

#### Examples¶

Check, if the word finishes with the word WORK.

SELECT <col_1> LIKE '%WORK'
FROM <table_name>


Check, if the string matches a phone number pattern.

SELECT <col_1> LIKE '(___) ___-____'
FROM <table_name>


### IN¶

Operator to check if values in a column are found on a specified list. The list can be literals or a subquery. Check the dedicated section on IN operators.

## Logical operators¶

Supported datatypes: BOOLEAN

Logical operators allow to chain multiple boolean columns or expressions that return boolean value to obtain more complex logical expressions.

### AND¶

The logical conjunction operator returns TRUE value if and only if both inputs are TRUE.

Truth table for AND

Input 1

Input 2

Result

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

TRUE

FALSE

FALSE

TRUE

TRUE

TRUE

FALSE

NULL

FALSE

TRUE

NULL

NULL

NULL

TRUE

NULL

NULL

FALSE

FALSE

NULL

NULL

NULL

#### Examples¶

Simple conjunction of two boolean columns.

SELECT <col_1_bool> AND <col_2_bool>
FROM <table_name>


Negated column syntax.

SELECT <col_1_bool> AND NOT <col_2_bool>
FROM <table_name>


### OR¶

The logical disjunction (or alternative) operator returns TRUE value if one of the inputs is TRUE.

Truth table for OR

Input 1

Input 2

Result

FALSE

FALSE

FALSE

FALSE

TRUE

TRUE

TRUE

FALSE

TRUE

TRUE

TRUE

TRUE

FALSE

NULL

NULL

TRUE

NULL

TRUE

NULL

TRUE

TRUE

NULL

FALSE

NULL

NULL

NULL

NULL

#### Examples¶

Simple disjunction of two boolean columns.

SELECT <col_1_bool> OR <col_2_bool>
FROM <table_name>


Negated column syntax.

SELECT <col_1_bool> OR NOT <col_2_bool>
FROM <table_name>


### NOT¶

The logical negation operator returns the reverse truth value.

Truth table for NOT

Input

Result

FALSE

TRUE

TRUE

FALSE

NULL

NULL

#### Example¶

Simple negation of a boolean column.

SELECT NOT <col_1_bool>
FROM <table_name>


## IN operators¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

The IN operator compares the values in a column with a list of allowed literals or values returned from a subquery, and returns a TRUE if the value is found.

### Finding matches¶

The syntax flags all the values that are found on the list.

#### Examples¶

Compare with a list of literals.

SELECT <col_1> IN ('literal_1', 'literal_2')
FROM <table_name>


Compare with a list generated by a subquery.

SELECT <col_1> IN (
SELECT <col_2>
FROM <table_2>
)
FROM <table_1>


### Inverting the matches¶

The syntax flags all the values that are found on the list. The comparisons can also be negated thus flagging all the values that are not found in the list

SELECT <col_1> NOT IN ('literal_1', 'literal_2')
FROM <table_name>


or query

SELECT <col_1> NOT IN (
SELECT <col_2>
FROM <table_2>
)
FROM <table_1>


## IS operators¶

The IS operators test if something is or is not. These operators never return NULL.

### IS [NOT] NULL¶

Supported datatypes: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE TIMESTAMP, VARCHAR, BOOLEAN

Tests if values in the column are NULL.

#### Example¶

Check if values are NULL.

SELECT <col_1> IS NULL
FROM <table_name>


Check if values are not NULL.

SELECT <col_1> IS NULL
FROM <table_name>


### IS [NOT] TRUE and IS [NOT] FALSE¶

Supported datatypes: BOOLEAN

Tests if values in the column are TRUE or FALSE.

#### Example¶

Check if values are TRUE.

SELECT <col_1> IS TRUE
FROM <table_name>


An equivalent would be to test if the values are not FALSE

SELECT <col_1> IS NOT FALSE
FROM <table_name>


Check if values are FALSE.

SELECT <col_1> IS FALSE
FROM <table_name>


Alternatively:

SELECT <col_1> IS NOT TRUE
FROM <table_name>


## CONCAT operator¶

Supported datatypes: VARCHAR

The concatenation operator joins two or more string type values into one.

CONCAT

### || operator¶

Combines two or more string type values (columns or literals) into one.

#### Example¶

Concatenate two text columns.

SELECT <col_1> || <col_2>
FROM <table_name>


Concatenate a column with a string.

SELECT <col_1> || <string_literal>
FROM <table_name>


Add underscore between text values in two columns.

SELECT <col_1> || '_' || <col_2>
FROM <table_name>