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.
|% (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.
|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.
|& (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
Comparison Operators: Comparison operators test whether two expressions are the same.
|= (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.