SQL For Beginners - Operators

SQL provides a wide range of operators which can be used to perform a varied number of operations. It is very important to know about these operators so that we can create queries which contains these operators and help us retrieve data as per our choice. 

 

The various types of operators in SQL are:

1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators

Note:

There are many other types of operators like Set Operators, Concatenation Operator, Scope Resolution Operator, etc. We are learning only about Arithmetic, Logical and Comparison Operators over here as they are the most widely used. Refer the complete list of various operators and their meaning here.

 

(I) Arithmetic Operators:

• + , - , * , / and % are the five arithmetic operators in SQL. 
• + (plus) is the addition operator. 
• - (minus) is the subtraction operator.
• Similarly, * (asterisk) is Multiplication operator and / (slash) is division operator. The operators work in the similar way as our simple mathematics.
• % (mod) is the modulus operator. It is important to remember that % and / are different. % gives the remainder while / gives the quotient.

   Example:

   

   Suppose if we query,

 

   Select 10-5;

 

   Then the result will be 5. 

 

   Similarly, if we have two columns a and b in table numbers. a contains 10 and b contains 5, then:

 

   SELECT a%b FROM Numbers;

   This will return 0 as when we divide 10 by 5, the remainder is zero.

 

(II) Logical Operators:

•  AND, NOT, OR, BETWEEN, IN, LIKE, EXISTS are the most commonly Logical Operators in SQL.
• There are many other logical operators like SOME, ALL, ANY, etc.
  
  Example:
  
  As we have already seen examples of AND and OR operators in the previous article SQL For Beginners -  WHERE Clause, let us see few other examples of the logical operators here. 

BETWEEN Operator (Also known as Range Operator):

 
SELECT * FROM Students

WHERE Marks BETWEEN 50 AND 70;

Output

 

As you can see in the output, the query retrieves all the details of the Students who have marks in the range 50 to 70. This is because, BETWEEN Operator returns true only for the records/values which are in the specified range. For anything which is not in the given range, the BETWEEN operator returns false.

 

NOT Operator

 

SELECT * FROM Students

WHERE Marks NOT BETWEEN 50 AND 70;

 

Output

 

Here, the NOT Operator negates the result of the BETWEEN Operator. The Between operator returns true for the records of StudentIDs 2,3 and 5 who have scored marks between 50 and 70 and the between operator returns false for the StudentIDs 1 and 4. But the Not operator negates this result and gives us the output as seen in the screenshot.

 

We will learn about the other Logical Operators in detail in the upcoming articles.

 

(III) Comparison Operators: 

• As the name suggests, comparison operators are used to compare 2 values/expressions.
• =, >, <, >=, <=, <>, !=, !>, !< are the various comparison operators in SQL.
• = means Equal to. > and < means greater than and less than respectively.
• >= is greater than or equal to.
• <= is less than or equal to.
• <> and != means not equal to.
• Similarly, !> and !< means not greater than and not less than respectively.

   Example:

 

   Sam has scored 88 marks. But, we want all the student's details except that of Sam. How do we achieve this? 

   

   SELECT * FROM Students

   WHERE marks<>88;

 

 

 

Here, as we mentioned in the query that we want all the details of students where marks is not equal to 88, we are getting all the details of the students who have not scored 88. As Sam has scored 88 marks, his details are not returned. 

Try predicting the output of the following queries and write your answer in the comment section:

1. SELECT * FROM Students Where StudentName='Raj';
2. SELECT StudentName FROM Students Where marks!>20;
3. SELECT 10%4; 

In the next article, we will learn about other important operators. Stay tuned! Keep learning!