Post
Ask Question

Operators In SQL Server

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.
 
OperatorMeaning
+Add
-Subtraction
*Multiplication
/Divide
%Modulo

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: =

Example

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

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 <,>,<=,>=,!=

Example

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

Union fetches all the values from the tables without Duplicates.

Example

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

 
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  
Result

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

Intersect

It returns the common values from the Tables.

Example

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  
Result

 
Except

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  
Result

Special Operators

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

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.
Example

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

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.
Example

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

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

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.
Example

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)