SQL SERVER – Types Of Operators

Operator is a reserved word or a character which can be used in WHERE Clause of a SELECT statement to add additional criteria to the retrieval statement.  These operators can also be used as a conjunction to multiple conditions in a statement

Different types of Operators that are available in SQL Server are :

1. Arithmetic Operators                                                  2. Logical Operators                                                      3. Assignment Operator                                                4. Bitwise Operators                                                     5. String Concatenation Operator                                    6. Comparison Operators                                                 7. Unary Operators

Arithmetic Operators :  Used to perform the mathematical operations on two expressions.

Add(+)  - To add two numbers

Example:                                                                            

Suppose an Organization has been announced Rs.5000 bonus to all of its employees. Now we want to see the Original salary and Salary after Bonus of all the employees.For this we will use the Add Arithmetic Operator.

Select EmpId, Empname, Salary, (Salary + 5000) Salary_with_Bonus From  Employee

Output :

Subtract(-) – To get the Difference between two Numbers or two Dates

Example 1: To get the difference between the maximum and minimum salaries

Select Max(Salary) Max_salary, Min(Salary)Min_Salary,    (Max(Salary) - Min(Salary)) Salary_Difference               From  Employee

Output :

Example 2:  To get the Date of Last week

Select  Getdate() Today, Getdate() – 7  Previous_week

Multiply(*) – To Multiply two Expressions 

Example :  To Calculate the Annual Income of all Employees

Select  EmpId, EmpName, Salary, (Salary * 12)  Annual_Income  From  Employee

Output :

Divide(/) – To Divide one number by another

Example :  To Calculate the Commission on the Salary for all Employees 

Select  EmpId,EmpName,Salary,(Salary * 2)/100 Commission From  Employee

Output :

Modulo(%) – To get the Integer Remainder of a Division

Example :  To calculate the modulo of the integer 5 divided by 3

Select  5%3

Output :

Logical Operators : Used to test the Truth of some condition and returns a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.

AND - Combines two Boolean expressions and returns TRUE when both expressions are TRUE

Example : 

Select * From Employee Where DeptId =1 AND Salary <25000

Output :

OR - Combines two Boolean expressions and returns TRUE when either of the expression is TRUE

Example : 

Select * From Employee Where DeptId =1 OR Salary <25000

Output :

NOT – Reverses the value of any Boolean Operator.

Example : 

Select * From Employee Where Not (DeptId =1)

Output :

Assignment Operator : Used to assign a value to variable. =’ is the only assignment operator in SQL Server.

Example : 

Declare @Number Int
Set @Number = 27
Select @Number Number

Output :

Bitwise Operators : Used to perform Bitwise Manipulations Between two expressions.

Bitwise AND (&) -  Performs a bitwise logical AND operation between two integer values

Example :  To get the Bitwise Logical AND of 100 (i.e. 0000  0000 0110 0100) and 50 (i.e. 0000  0000 0011 0010)

Select 100 & 50 'Bitwise Logical AND'

Output :

Bitwise OR (|) -  Performs a bitwise logical OR operation between two integer values

Example :  To get the Bitwise Logical OR of 100 (i.e. 0000  0000 0110 0100) and 50 (i.e. 0000  0000 0011 0010)

Select 100 | 50 'Bitwise Logical OR'

Output :

Bitwise Exclusive OR (^) -  Performs a bitwise exclusive  OR operation between two integer values.

Example :  To get the Bitwise Exclusive OR of 100 (i.e. 0000  0000 0110 0100) and 50 (i.e. 0000  0000 0011 0010)

Select 100 ^ 50 'Bitwise Exclusive OR'

Output :

String Concatenation Operator :  Used to Concatenate two or more Strings. ‘+’ is the  String Concatenation Operator in SQL Server.

Example : 

Select 'SQL SERVER ' + '2008'  'Database'

Output :

Comparison Operators :  Used to Compare the expressions whether the two expressions are same.Different Operators available are : Equals to(=), Greater Than(>),Less Than(<),Greater Than or Equal To(>=),Less Than or Equal To(<=),Not Equal To(<>).

Example : 

Declare @Salary  Money
Set @Salary = 25000
IF (@Salary <> 0)
Select * From Employee
Where Salary <= @Salary

Output :

Unary Operators :  Performs the operation on single expression of any data type that relates to Numeric Category.

Positive Or Unary Plus(+)  - Returns the Positive value of a numeric expression.

Negative Or Unary Minus(-)  - Returns the Negative value of a numeric expression.

Positive Or Unary Plus(+)  - Returns the Complement of the numeric expression.

Example : 

DECLARE @MyNumber1 int ,@MyNumber2 int,@MyNumber3 int
SET @MyNumber1 = +5
SET @MyNumber2 = -5
SET @MyNumber3 = ~4
SELECT @MyNumber1 'Unary Plus' , @MyNumber2 'Unary Minus' ,@MyNumber3 'Bitwise Not'

Output :