SQL Operators

Introduction to SQL Operators

SQL Operators are defined as a symbol or special reserved keywords used to specify some action that is performed on the given expression which includes several clauses to tell the system how the operators behave. Operators do calculations between the data items or operand and execute the Query result. It provides a condition in SQL statements or also combines multiple conditions and executes them between the operands (filters the results). The operators which are used between the data can come with single or with binary operands. All the operators are specified with the conditional statements along with the WHERE clause of SQL.

Types of SQL Operators

SQL is the foundation of database management systems that deals with a variety of manipulation of data. therefore, SQL provides some SQL Operators to perform Operations and while Evaluating the Expression order of precedence is very important. Following are the various Operators used in SQls.

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

Let’s see One by one in detail:

1. Arithmetic Operators

These operators are used to manipulate mathematical calibrations like addition, multiplication, division, subtraction and other modulus numeric values in the SQL query.

Syntax:

Select <expression 1> operator <expression 2>

1. Addition

This operator Helps in adding values on both sides of the operators. Below are the unary Operators. Example: M + N

Code:

SQL> select 11+ 20;

Output:

2. Subtraction

This operator implies subtracting values on both sides of the operator (right value from the left). Example: M – N

Code:

SQL> select 30- 20;

Output:

3. Multiplication

This SQL operator does multiplication operation between two operands. Example: M*N

Code:

SQL> select 12 * 6;

Output:

4. Division

This operator performs actions by dividing one expression by the other expressions. Example: M/N

Code:

SQL> select 120 / 5;

Output:

5. Modulus

This SQL operator returns the remainder of the division process between two operands. The following statement for this is:

Code:

SQL> select 12 / 7;

Output:

• Below Query using Arithmetic Operation on a specific Database Table. Consider a table ‘Finance’ with four Columns.

• Using Arithmetic Operators on the Specific fields On the Table. Here I have taken the Salary column to perform the Arithmetic operation.

Example #1

Code:

SELECT fid, FName, salary, salary + 50
AS "Revised Sal" FROM finance;

Output:

Example #2

Code:

SELECT fid, FName, salary, salary - 150
AS "Revised Sal" FROM finance;

Output:

• That all about the Arithmetic operations of SQL. In the next section, we shall see Comparison operators

2. Comparison Operators or Relational Operators

Being conditional takes two expressions and does the valuable comparison and returns either True or False. It does the operations such as equal to, lesser than, greater than or greater than equal to and other advanced concepts too. This operator joins their hands with the ‘where’ clause to select the particular columns in the records. Here in Below Section, we describe different types of relational operators and few examples on them along with the syntax.

Syntax:

SELECT column FROM table WHERE condition1 Relational Operator condition2;

1. Equal to (=)

This operator checks the value of the two operands is the same or not. If it is equal then it returns true, if not returns false.

Code:

SELECT FName, salary FROM finance where salary =5000;

Output:

2. Not equal to or Inequality (! =), (<>)

It verifies whether the two operands’ values are equal or not. If they are not equal then the statement returns True.

Code:

SELECT FName, salary FROM finance where salary <>2500;

Output:

3. Greater than (>)

It is used in SQL to check for the greater than a value between two operands.

Code:

SELECT FName, salary FROM finance where salary>5000;

Output:

4. Greater than equal to (> =)

It is used in SQL to check for the greater than or equal to a value between two operands.

Code:

SELECT FName, salary FROM finance where salary>=5000;

Output:

5. Lesser than (<)

This operator in SQL is used to check whether a left operand is lesser than the right operand. If it is true it results in the result.

Code:

SELECT FName, salary FROM finance where salary < 5000;

Output:

6. Lesser than or Equal to (<=)

This operator in SQL is used to check whether a left operand is lesser than or equal to the right operand. If it is true it results in the result.

Code:

SELECT FName, salary FROM finance where salary <=4500;

Output:

Some advanced operator is Like, IS Not Null, Between which we shall see later.

3. Logical Operators

Logical operations in the SQL which is true or False are performed by the Logical Operators. Different operators used here are listed below:

• AND
• OR
• NOT
• BETWEEN
• ANY

Syntax:

SELECT col name  * | expr [logical operator] [col name | * | expr..] FROM tablename
WHERE <expr> [ logical operator |
arithmetic operator | ...] <expressions>;

Considering the same database Finance to perform the Logical operators:

1. AND Operator

It gives preference to make use of multiple conditions with the WHERE clause.

Code:
SELECT  fid,FName,address,salary
FROM finance
WHERE  FName  =  'Leslia' AND  salary  =  2500;

Output:

2. OR

This makes a comparison between the expressions in the statement and returns true if either of the condition is True.

Code:

SELECT  fid,FName,address,salary
FROM finance
WHERE  FName  =  'Gruce TOM' OR salary  =  2500;

Output:

3. NOT

This operator takes an argument as a single Boolean and returns true if it is false and vice-versa.

Code:

SELECT Fid , address FROM finance WHERE NOT FName = "Dany";

Output:

4. ANY

A specific value is taken and it is compared with any of the values in the fields.

Code:

SELECT * FROM finance
WHERE salary > ANY (SELECT salary FROM finance WHERE salary > 5000);

Output:

5. BETWEEN

This operator is used when there is a limit range between the values.

Code:

SELECT * FROM finance
WHERE salary BETWEEN 2500 AND 5000;

Output:

Conclusion

Therefore, in this article, we have learned how to use different operators in SQL Statements with an implementation. Also, we came across the filtering of data from the database using respective conditions as the operators allow in checking two expressions to be the same.

Recommended Articles

This is a guide to SQL Operators. Here we discuss the basic concept with 3 different types of SQL in detail with appropriate codes and outputs respectively. You can also go through our other related articles to learn more –

1. SQL Set Operators
2. SQL Clauses
3. PostgreSQL ORDER BY
4. SQL String Operators