SQL Operators


What is SQL Operator?

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.

Types of Operator in SQL

SQL supports following types of operators:

1- Arithmetic Operators

2- Comparison Operators

3- Logical 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.

OperatorDescriptionExample
+Addition10 + 20 = 30
-Subtraction20 - 30 = -10
*Multiplication10 * 20 = 200
/Division20 / 10 = 2
%Modulus5 % 2 = 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 operation 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.

OperatorDescriptionExample
=Equal to5 = 5 returns TRUE
!=Not equal5 != 6 returns TRUE
<>Not equal5 <> 4 returns TRUE
>Greater than4 > 5 returns FALSE
<Less than4 < 5 returns TRUE
>=Greater than or equal to4 >= 5 returns FALSE
<=Less than or equal to4 <= 5 returns TRUE
!<Not less than4 !< 5 returns FALSE
!>Not greater than4 !> 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 operation 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.

OperatorDescription
ALLTRUE if all of a set of comparisons are TRUE.
ANDTRUE if all the conditions separated by AND are TRUE.
ANYTRUE if any one of a set of comparisons are TRUE.
BETWEENTRUE if the operand lies within the range of comparisons.
EXISTSTRUE if the subquery returns one or more records
INTRUE if the operand is equal to one of a list of expressions.
LIKETRUE if the operand matches a pattern specially with wildcard.
NOTReverses the value of any other Boolean operator.
ORTRUE if any of the conditions separated by OR is TRUE
IS NULLTRUE if the expression value is NULL.
SOMETRUE if some of a set of comparisons are TRUE.
UNIQUEThe UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL Operator Precedence

The operator 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.

OperatorOperation
+, -identity, negation
*, /multiplication, division
+, -addition, subtraction
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, INComparison
NOTlogical negation
ANDconjunction
ORinclusion

Example

Consider the following SQL statement:

SELECT 50 - 8 * 4;

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

18

Talk to us?

Post your blog