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 aunaryorbinary 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

SQLsupports following types of operators:

Arithmetic operators
Comparison operators
Logical operators
Operators used to negate conditions

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 aBooleanvalue 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.

Operator	Description	Example
`=`	Equal to	`5 = 5` returns TRUE
`!=`	Not equal	`5 != 6` returns TRUE
`<>`	Not equal	`5 <> 4` returns TRUE
`>`	Greater than	`4 > 5` returns FALSE
`<`	Less than	`4 < 5` returns TRUE
`>=`	Greater than or equal to	`4 >= 5` returns FALSE
`<=`	Less than or equal to	`4 <= 5` returns TRUE
`!<`	Not less than	`4 !< 5` returns FALSE
`!>`	Not greater than	`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 aBooleanvalue 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.

OperatorDescriptionExampleALLTRUE if all of a set of comparisons are TRUE.ExampleANDTRUE if all the conditions separated by AND are TRUE.ExampleANYTRUE if any one of a set of comparisons are TRUE.ExampleBETWEENTRUE if the operand lies within the range of comparisons.ExampleEXISTSTRUE if the subquery returns one or more recordsExampleINTRUE if the operand is equal to one of a list of expressions.ExampleLIKETRUE if the operand matches a pattern specially with wildcard.ExampleNOTReverses the value of any other Boolean operator.ExampleORTRUE if any of the conditions separated by OR is TRUEExampleIS NULLTRUE if the expression value is NULL.ExampleSOMETRUE if some of a set of comparisons are TRUE.ExampleUNIQUEThe UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).Example

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, IN Comparison NOTlogical negationANDconjunctionORinclusion

Example

Consider the following SQL statement:

SQL CODE

SELECT 20 - 3 * 5;

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

SQL CODE