Logical and Bitwise Operators

Logical NOT (!)

Function: Performs a logical NOT operation on an expression, i.e., if the expression is true, returns false; if the expression is false, returns true.

Parameters:

• expr: Boolean expression.

Return Result:

• Boolean value.

Example:

SELECT !true; -- Returns false
SELECT !false; -- Returns true
SELECT !NULL; -- Returns NULL

Bitwise NOT (~)

Function: Performs a bitwise NOT operation on the expression, which means turning every 0 into 1 and every 1 into 0.

Parameters:

• expr: An integer type expression.

Return Result:

• A value of the same type as the input expression.

Example:

SELECT ~0; -- Returns -1

Not Equal (!=)

Function: Compares two expressions and returns true if they are not equal.

Parameters:

• expr1: Boolean type expression.
• expr2: Expression of the same type as expr1.

Return Result:

• Boolean value.

Example:

SELECT 1 != 0; -- Returns true
SELECT 10 != 10; -- Returns false
SELECT 10 != NULL; -- Returns NULL

Modulus (%)

Function: Returns the remainder of the division of two numeric expressions.

Parameters:

• expr1: Numeric type expression, including float, double, decimal, tinyint, smallint, int, bigint.
• expr2: Expression of the same type as expr1.

Return Value:

• Value of the same type as the input parameters.

Example:

SELECT 2 % 1.8; -- Returns 0.2
SELECT -10 % 3; -- Returns -1

Multiplication (*)

Function: Returns the product of two numeric expressions.

Parameters:

• expr1: Numeric type expression, including float, double, decimal, tinyint, smallint, int, bigint.
• expr2: Expression of the same type as expr1.

Return Result:

• Value of the same type as the input parameters.

Example:

SELECT 1.4 * 2.3; -- Returns 3.22

Addition (+)

Function: Returns the sum of two numeric expressions.

Parameters:

• expr1: Numeric type expression, including float, double, decimal, tinyint, smallint, int, bigint.
• expr2: Expression of the same type as expr1.

Return Result:

• Value of the same type as the input parameters.

Example:

SELECT 1.4 + 2.3; -- Returns 3.7

Positive Sign (+)

Function: Returns the value of the expression without making any changes.

Parameters:

• expr: Numeric type expression.

Return Result:

• Value of the same type as the input parameter.

Example:

SELECT +10; -- Returns 10
SELECT +(-10); -- Returns -10

Subtraction (-)

Function: Returns the difference between two numeric expressions.

Parameters:

• expr1: Numeric type expression, including float, double, decimal, tinyint, smallint, int, bigint.
• expr2: Expression of the same type as expr1.

Return Result:

• Value of the same type as the input parameters.

Example:

SELECT 3.3 - 10; -- Returns -6.7

Negative Sign (-)

Function: Returns the negative value of the expression.

Parameters:

• expr: Numeric type expression.

Return Result:

• Value of the same type as the input parameter.

Example:

SELECT -(-10); -- Returns 10
SELECT -(1.1); -- Returns -1.1

Division (/)

Function: Returns the quotient of dividing two numeric expressions.

Parameters:

• expr1: Numeric type expression, including float, double, decimal.
• expr2: Expression of the same type as expr1.

Return Result:

• Value of the same type as the input parameters.

Example:

SELECT 3.3 / 1.5; -- Returns 2.2
SELECT 9L / 2L; -- Returns 4.5