SQL Operators
SQL Operators are used in SELECT statements to perform mathematical and logical operations on operands.
FeatureBase returns no results where operator logic is incorrect
Before you begin
Unary Operators
Sign
| Operator | Description | Example | Result |
+ or - | apply a sign to a numeric data type | SELECT -1 | -1 |
Bitwise
| Operator | Description | Example | Binary representation | Binary Result |
! | Not inverts all the bits of the operand using Two’s complement | SELECT !5; | 0101 | 1010 –> (-8)+(0)+(2)+(0) = -6 |
Binary Operators
Logical
| Operator | WHERE condition returns true when… | Examples |
= | both operands are equal. | select _id from products WHERE price = 52.50; |
!= | both operands are not equal. | select stock from products WHERE price != 0.50; |
<> | both operands are not equal. | select stock from products WHERE price <> 0.50; |
AND | both conditions match the operand. | select * from products WHERE stock > 20 AND price < 10; |
BETWEEN | the operand is within the range of two integer values | select * from products WHERE stock BETWEEN 0 AND 100; |
LIKE | the operand matches the specified pattern. | select * from products WHERE prodlist LIKE '%play%'; |
OR | either condition matches the operand | select * from products WHERE prodlist LIKE '%pla%' OR stock < 20; |
Comparison
| Operator | WHERE condition returns True when… | Examples |
< | the operand on the left is less than the operand on the right. | SELECT 1 WHERE 10 < 100; |
> | the operand on the left is greater than the operand on the right. | SELECT 1 WHERE 10 > 1; |
<= | the operand on the left is less than or equal to the operand on the right. | SELECT 1 WHERE 10 >= 10; |
>= | the operand on the left is greater than or equal to the operand on the right. | SELECT 1 WHERE 10 <= 11; |
Arithmetic
| Operator | Description | Examples |
+ | Add two operands. | SELECT 1+1; |
- | Subtract two operands. | SELECT 1-1; |
* | Multiply two operands. | SELECT 12*11; |
/ | Divide two operands. | SELECT 120/12; |
% | Modulo divides two operands then returns the remainder or signed remainder. | SELECT 5%3; |
Bitwise
| Operator | Description | Example | Binary representation | Binary Result |
& | And sets each bit to 1 where both bits are 1 | SELECT 5 & 1 | 0101 & 0001 | 0001 |
| | Or sets each bit to 1 if one of two bits = 1 | SELECT 5 | 1 | 0101 | 0001 | 0101 |
<< | The second operand determines the number of zeroes to insert to the right which then shifts left the value of the first operand. | SELECT 5 << 1; | 0101 << 1 | 1010 |
>> | The second operand determines the number of zeroes to remove from the right which then shifts right the value of the first operand. | SELECT 5 >> 1; | 0101 >> 1 | 0010 |
String
| Operator | Description | Example | Result |
|| | Concatenate two strings into a single string. | SELECT 'CON' || 'CAT'; | CONCAT |
FeatureBase also supports standard operators: