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 |
Operators |
|---|---|
1 |
parentheses |
2 |
multiplication, addition, string concatenation |
3 |
addition, subtraction |
4 |
comparison operators, |
|
|
|
|
5 |
|
6 |
|
7 |
|
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.
Addition¶
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.
Input 1 |
Input 2 |
Result |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
Input 1 |
Input 2 |
Result |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
Input |
Result |
|---|---|
|
|
|
|
|
|
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.
See also
|| 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>