Teach with us

SQL - Operators

Previous Page
Next Page  

What is an Operator in SQL?

An SQL operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

An SQL operator can be either a unary or binary operator. A unary operator (example unary + or unary - ) uses only one operand to perform the unary operation, whereas the binary operator (example + or - etc) uses two operands to perform the binary operation.

SQL supports following types of operators:

SQL Arithmetic Operators

SQL Arithmetic Operators are used to perform mathematical operations on the numerical values. SQL provides following operators to perform mathematical operations.

Here is a list of all the arithmetic operators available in SQL.

Operator Description Example
+ Adds the values both sides of the operator. 10 + 20 gives 30
- Subtracts right hand operand from left hand operand. 20 - 30 gives -10
* Multiplies the values from both sides of the operator. 10 * 20 gives 200
/ Divides left hand operand by right hand operand. 20 / 10 gives 2
% Divides left hand operand by right hand operand and returns the reminder remainder. 5 % 2 gives 1

SQL Comparison Operators

SQL Comparison Operators test whether two given expressions are the same or not. These operators are used in SQL conditional statements while comparing one expression with another and they return a Boolean value which can be either TRUE or FALSE. The result of an SQL comparison operatoration can be UNKNOWN when one or another operand has it’s value as NULL.

Here is a list of all the comparison operators available in SQL.

Operator Description Example
= Checks if the values of two operands are equal or not, if yes then condition becomes true. 5 = 5 returns TRUE
!= Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. 5 != 6 returns TRUE
<> Checks if the values of two operands are equal or not, if values are not equal then condition becomes true. 5 <> 4 returns TRUE
> Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. 4 > 5 returns FALSE
< Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. 4 < 5 returns TRUE
>= Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. 4 >= 5 returns FALSE
<= Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. 4 <= 5 returns TRUE
!< Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true. 4 !< 5 returns FALSE
!> Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true. 4 !> 5 returns TRUE

SQL Logical Operators

SQL Logical Operators are very similar to comparison operators and they test for the truth of some given condition. These operators return a Boolean value which can be either a TRUE or FALSE. The result of an SQL logical operatoration can be UNKNOWN when one or another operand has it’s value as NULL.

Here is a list of all the logical operators available in SQL.

Operator Description Example
ALL TRUE if all of a set of comparisons are TRUE.
AND TRUE if all the conditions separated by AND are TRUE.
ANY TRUE if any one of a set of comparisons are TRUE.
BETWEEN TRUE if the operand lies within the range of comparisons.
EXISTS TRUE if the subquery returns one or more records
IN TRUE if the operand is equal to one of a list of expressions.
LIKE TRUE if the operand matches a pattern specially with wildcard.
NOT Reverses the value of any other Boolean operator.
OR TRUE if any of the conditions separated by OR is TRUE
IS NULL TRUE if the expression value is NULL.
SOME TRUE if some of a set of comparisons are TRUE.
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL Operators Precedence

The operators precedence in SQL is the sequence in which the SQL evaluates the different operators in a given expression. The operators with higher precedence get evaluated first.

Following table lists all SQL operators as per their precedence. The operators with the highest precedence are at the top and the operators with the lowest precedence are at the bottom.

Operator Operation
+, - identity, negation
*, / multiplication, division
+, - addition, subtraction
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN Comparison
NOT logical negation
AND conjunction
OR inclusion

Example

Consider the following SQL statement:

SELECT 20 - 3 * 5

This will result in the following. Here multiplication operator gets evaluated first and then subtraction happens.

5
Previous Page Print Page Next Page  
Advertisements