SQL Server Operators
Hi friends, in this post we will discuss about SQL Server Operators. Operator is a symbol specifying an action performed on one or more expressions. Operators are used to specify conditions in an SQL statement.
Following are operators in SQL Server:
Arithmetic Operators: Arithmetic operators used to perform mathematical operations on two expressions.
| Operator | Meaning | Example |
| + (Add) | Addition | 4+2=6 |
| – (Subtract) | Subtraction | 4-2=2 |
| * (Multiply) | Multiplication | 4*2=8 |
| / (Divide) | Division | 4/2=2 |
| % (Modulo) | Returns the integer remainder of a division. | 0 |
Logical Operators: Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.
| Operator | Description |
| ALL | The ALL operator is used to compare a value to all values in another value set. |
| AND | The AND operator allows the existence of multiple conditions in an SQL statement’s WHERE clause. |
| ANY | The ANY operator is used to compare a value to any applicable value in the list according to the condition. |
| BETWEEN | The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. |
| EXISTS | The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria. |
| IN | The IN operator is used to compare a value to a list of literal values that have been specified. |
| LIKE | The LIKE operator is used to compare a value to similar values using wildcard operators. |
| NOT | The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator. |
| OR | The OR operator is used to combine multiple conditions in an SQL statement’s WHERE clause. |
| IS NULL | The NULL operator is used to compare a value with a NULL value. |
| UNIQUE | The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates). |
Assignment Operator: The equal sign (=) is the only Transact-SQL assignment operator.
Scope Resolution Operator: The scope resolution operator :: provides access to static members of a compound data type.
Bitwise Operators: Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.
| Operator | Meaning |
| & (Bitwise AND) | Bitwise AND (two operands). |
| | (Bitwise OR) | Bitwise OR (two operands). |
| ^ (Bitwise Exclusive OR) | Bitwise exclusive OR (two operands). |
Set Operators: Set operators combine results from two or more queries into a single result set.
EXCEPT and INTERSECT
UNION
Comparison Operators: Comparison operators test whether two expressions are the same.
| Operator | Meaning |
| = (Equals) | Equal to |
| > (Greater Than) | Greater than |
| < (Less Than) | Less than |
| >= (Greater Than or Equal To) | Greater than or equal to |
| <= (Less Than or Equal To) | Less than or equal to |
| <> (Not Equal To) | Not equal to |
| != (Not Equal To) | Not equal to (not ISO standard) |
| !< (Not Less Than) | Not less than (not ISO standard) |
| !> (Not Greater Than) | Not greater than (not ISO standard) |
String Concatenation Operator: SQL Server provides the following string operators. String concatenation operators can combine two or more character or binary strings, columns, or a combination of strings and column names into one expression.
+ (String Concatenation)
+= (String Concatenation)
% (Wildcard – Character(s) to Match)
[ ] (Wildcard – Character(s) to Match)
[^] (Wildcard – Character(s) Not to Match)
_ (Wildcard – Match One Character)
Compound Operators: Compound operators execute some operation and set an original value to the result of the operation.
Unary Operators: Unary operators perform an operation on only one expression of any one of the data types of the numeric data type category.