SQL Operators
An operator is a reserved word or a character used primarily in an SQL statement 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.
SQL operators are used for filtering the table's data by a specific condition in the SQL statement.
Types of Operator
SQL operators are categorized in the following categories:
- SQL Arithmetic Operators
- SQL Comparison Operators
- SQL Logical Operators
Precedence of SQL Operator
The precedence of SQL operators is the sequence in which the SQL evaluates the different operators in the same expression. Structured Query Language evaluates those operators first, which have high precedence.
SQL Operator Symbols | Operators |
** | Exponentiation operator |
=+, - | Identity operator, Negation operator |
*, / | Multiplication operator, Division operator |
=+, -, || | Addition (plus) operator, subtraction (minus) operator, String Concatenation operator |
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN | Comparison Operators |
NOT | Logical negation operator |
&& or AND | Conjunction operator |
OR | Inclusion operator |
1. Arithmetic Operators
Arithmetic operators are used to perform arithmetic operations such as addition, subtraction, division, and multiplication. These operators usually accept numeric operands.
Operator | Operation | Description |
+ | Addition | Adds operands on either side of the operator |
- | Subtraction | Subtracts the right-hand operand from the left-hand operand |
* | Multiplication | Multiplies the values on each side |
/ | Division | Divides left-hand operand by right-hand operand |
% | Modulus | Divides left-hand operand by right-hand operand and returns the remainder |
2. Comparison Operators
The comparison operators in SQL compare two different sets of data from SQL tables and check whether they are the same, greater, or lesser. It checks whether one expression is identical to another. Comparison operators are generally used in the WHERE clause of a SQL query. The result of a comparison operation may be true, false, or unknown. When one or both of the expressions are NULL, then the operator returns UNKNOWN. These operators could be used on all types of expressions except those that contain text, ntext or images.
Operator | Operation | Description |
= | Equal to | Checks if both operands have equal value, if yes, then returns TRUE |
> | Greater than | Checks if the value of the left-hand operand is greater than the right-hand operand or not |
< | Less than | Returns TRUE if the value of the left-hand operand is less than the value of the right-hand operand |
>= | Greater than or equal to | It checks if the value of the left-hand operand is greater than or equal to the value of the right-hand operand, if yes, then returns TRUE |
<= | Less than or equal to | Examines if the value of the left-hand operator is less than or equal to the right-hand operand |
<> or != | Not equal to | Checks if values on either side of the operator are equal or not. Returns TRUE if values are not equal |
!> | Not greater than | Used to check if the left-hand operator’s value is not greater than or equal to the right-hand operator’s value |
!< | Not less than | Used to check if the left-hand operator’s value is not less than or equal to the right-hand operator’s value |
3. Logical Operators
The logical operators in SQL perform the Boolean operations, which give two results: true and false. Logical operators take two expressions as operands and return TRUE or FALSE as output. While working with complex SQL statements and queries, comparison operators come in handy, and these operators work in the same way as logic gates do.
Operator | Operation | Description |
= | Equal to | Checks if both operands have equal value, if yes, then returns TRUE |
> | Greater than | Checks if the value of the left-hand operand is greater than the right-hand operand or not |
< | Less than | Returns TRUE if the value of the left-hand operand is less than the value of the right-hand operand |
>= | Greater than or equal to | It checks if the value of the left-hand operand is greater than or equal to the value of the right-hand operand, if yes, then returns TRUE |
<= | Less than or equal to | Examines if the value of the left-hand operator is less than or equal to the right-hand operand |
<> or != | Not equal to | Checks if values on either side of the operator are equal or not. Returns TRUE if values are not equal |
!> | Not greater than | Used to check if the left-hand operator’s value is not greater than or equal to the right-hand operator’s value |
!< | Not less than | Used to check if the left-hand operator’s value is not less than or equal to the right-hand operator’s value |
No comments: