Operators In SQL Server

An operator is a symbol which is used to perform some actions on the given expressions.

An operator is a symbol which is used to perform some action on given expressions.
  • Arithmetic Operators
  • Logical Operators
  • Set Operators
  • Comparison Operators
  • Special Operators
  • Assignment Operators
  • String Concat Operator
Arithmetic Operators

Arithmetic Operators are used for performing the mathematical operations on the given values.

Create a Table with a Name student.
  1. create Table student(id int,name varchar(50),Eng int,hindi int,math int,totalmarks int,avg int)  
Insert some values in the Table.
  1. insert into student(id,name,Eng,hindi,math)values(1,'A',65,58,49)  
  2. insert into student(id,name,Eng,hindi,math)values(2,'B',45,33,41)  
  3. insert into student(id,name,Eng,hindi,math)values(3,'C',65,88,35)  
  4. insert into student(id,name,Eng,hindi,math)values(4,'D',61,88,62)  
  5. insert into student(id,name,Eng,hindi,math)values(5,'E',41,81,81)  
Example - Write a query to find total marks and average marks of a student 
Total marks
  1. Update student set Totalmarks =Eng+hindi+math  
Avg Marks
  1. update student set avg=Totalmarks /3  
Select * from student

In the same manner, we can use others Arithmetic operators like -,*, %.

Assignment operator

The assignment operator is used to assign values to operands.

Assignment operator: =


Write a query to display student details whose id=4?
  1. Select * from student where id=4  

Comparison Operators

Comparison Operators are used for comparing values with the given specific conditions.

Set Operators

It is used to combine the result of 2 or more tables as a single set of values.

These operators are <,>,<=,>=,!=


Write a query to display student details whose avg marks are greater than 60.
  1. select * from student where avg>60  

Set Operators

It is used to combine the result of 2 or more Tables as a single set of values.

Type of Set Operators
  1. Union
  2. Union All
  3. Intersect
  4. Except
Some important Rules we should follow to use Set Operators
  1. Number of columns and order must be the same in all the queries.
  2. Columns Datatypes should be compatible.
Create two Tables
  1. Create table class_A(id int,studnetname varchar(50),Marks int,Age int)  
  2. Create table class_B(id int,studentname varchar(50),marks int ,Age int)  
Insert values in Table class_A
  1. insert into class_A values(1,'A',88,21)  
  2. insert into class_A values(2,'B',76,19)  
  3. insert into class_A values(3,'C',81,22)  
  4. insert into class_A values(4,'D',57,20)  
Insert values in Table class_B
  1. insert into class_B values(1,'A',88,21)  
  2. insert into class_B values(5,'E',57,20)  
  3. insert into class_B values(6,'P',57,20)  

Union fetches all the values from the tables without Duplicates.


Write a query to display all student details from both the classes,
  1. Select * from class_a  
  2. Union   
  3. Select * from class_b  


Union all

Union all is same as union but it returns duplicate values too. 
  1. Select * from class_a  
  2. Union all  
  3. Select * from class_b  


We can see that in the union it does not return the duplicate values while in the union all duplicate values are returned.


It returns the common values from the Tables.


Write a query to display students' names who are present in both the classes,
  1. Select studnetname from class_a  
  2. intersect  
  3. Select studentname from class_b  



Example - Write a query to display students' names who are in a class_a but not in class_b.
  1. Select studnetname from class_a  
  2. except  
  3. Select studentname from class_b  

Special Operators

These are special operators in SQL Server,
  1. Between
  2. In
  3. Like
  4. IsNull

Between is used to fetch values from a given Range. Between operator is to return the values from source values.
  • It can be applied on small to big range values only.
  • It doesn’t support Big to small values range.
  • We should use and operator when we implement between operators.

Write a query to get students whose age is between 22 to 26.
  1. Select * from class_A where age Between 20 and 22  

It works on the given list of values in the given condition.
  • It is an extension of Or operator.
  • The performance of In operator is faster than OR operator. When we use OR operator we will repeat the column names again and again in the query but when we use In operator there is no need to repeat the column name in the query.
  • In OR operator query length will be increased.

Write a query to display student details whose names are a,c,d.
  1. select * from class_A where studnetname in('a','c','d')  

IS NULL operator is to compare the values with Null in the table.
  • It occupies 0-byte memory.

Like is used to filter the values in the given expression or condition.

We can use like operator with this given expression. These are called Wildcard operators.
  1. % It represents any char in the given expression.
  2. -It represents a single char in the expression.
  3. []-it represents a group of char.

Write a query to display student details whose name starts with a.
  1. select * from class_A where studnetname like 'a%'  
Logical Operator

These are the logical operators,
  1. AND
  2. OR
  3. NOT
AND - AND is used to perform the operations when given conditions should be true.
OR - OR is used to perform some operations if any one condition is true from given conditions.
NOT - We can understand the NOT operator with the help of the following example.

String Concatenation Operator

String concatenation operators are used to combine two or more char or columns into a single expression.

Following are the String Concatenation Operator in SQL Server,
  • + (String Concatenation)
  • % (Wildcard Character)
  • [ ] (Wildcard Character)
  • _ (Wildcard Match One Character)