## SQL Operators :– Comparison, Arithmetic and Logical Operators

**Question** – What is an Operator?

**Answer –** As per simple definition, Operator are defined as a symbol which will perform some specific operation. Without operators, we can’t implement some operations, which is why operators are very helpful. Every operator is significant and performs a specific operation. The main purpose of using SQL Operators is to filter the data to be selected and are widely used in Expressions or Conditional statements of the WHERE and HAVING clause. Operators are the reserved words and used to perform operations like comparison, mathematical, etc.

We have different types of SQL operators.

- Arithmetical operators.
- Assignment operators.
- Comparison operators.
- Logical operators.
- String concatenation operators.
- Unary operators.

Among all the above mentioned SQL operators, we will mainly concentrate on 3 types of SQL Operators i.e. Comparison operators, Arithmetic operators and Logical operators. So let’s look into the various SQL operators one by one with practical implementation.

**SQL Comparison Operators : –**

This SQL operator is used to compare the column data with specific values.

Operators Name |
Description |
Example |

= (Equal) |
Value of Left operand is Equal (=) to Value of Right operand. |
A=B |

!= Or <> (Not equal) |
Value of Left operand is Not equal (!= Or <>) to Value of Right operand. |
A!=B |

> (Greater than) |
Value of Left operand is Greater than (>) Value of Right operand. |
A>B |

< (Less than) |
Value of Left operand is Less than (<) Value of Right operand. |
A<B |

>= (Greater than or Equal to) |
Value of Left operand is Greater than or Equal to (>=) Value of Right operand. |
A>=B |

<= (Less than or Equal to) |
Value of Left operand is Less than or Equal to (<=) Value of Right operand. |
A<=B |

Let’s see how Comparison operator works practically by considering some examples. We will create one table and will give name as employee_phpring in our database (PHPRING). After creating the mentioned table, we will insert some dummy data to look the working of Comparison SQL operator.

**–Creating a table employee_phpring in the PHPRING database**

1 2 3 |
use PHPRING go Create table employee_phpring (id int, name varchar(20), skills varchar(20), salary money, location char(10)); |

**–Insert few rows into employee_phpring to perform Comparison operations.**

1 2 3 4 5 6 7 |
Insert into employee_phpring values (1,'chander sharma','msbi',20000,'pune'), (2,'sai krishna','msbi',15000,'hyderabad'), (3,'pinal dave','sql server',40000,'bangalore'), (4,'vinod kumar','ms office',50000,'chennai'), (5,'balmukund','sql server',40000,'bangalore'), (6,'Avinash Reddy','msbi',10000,'hyderabad'); |

**–Applying comparison operators on the above created table.**

**1.** (=) – Equal to operator :-

Query to find the employee whose salary is equal to 20000.

1 |
Select * from employee_phpring WHERE salary=20000; |

**2.** (!= or <>) – Not Equal to operator :-

Query to find the employee whose salary is Not equal to 20000.

1 2 3 |
Select * from employee_phpring WHERE salary!=20000 OR Select * from employee_phpring WHERE salary<>20000; |

**3.** (>) – Greater than operator :-

Query to find the employee whose salary is greater than 20000.

1 |
Select * from employee_phpring WHERE salary>20000; |

**4.** (<) – Less than operator :-

Query to find the employee whose salary is greater than 20000.

1 |
Select * from employee_phpring WHERE salary<20000; |

**5.** (<=) – Lessthan or Equal to operator :-

Query to find the employee whose salary is Less than or equa lto 20000*/

1 |
Select * from employee_phpring WHERE salary<=20000; |

**6. (>=) **– Greater than or Equal to operator :-

Query to find the employee whose salary is greater than or equal to 20000*/

1 |
Select * from employee_phpring WHERE salary>=20000; |

**SQL Arithmetic Operators****: –**

This SQL operator is used with Number and Date data types.

Operators Name |
Description |
Example |

+ (Addition) |
Adds the Value of A to the Value of B. | A+B |

– (Subtraction) |
Subtracts the Value A from the Value of B. | A-B |

* (Multiplication) |
Multiply the Value of A and the Value of B. | A*B |

/ (Division) |
Divide the Value of A with the value of B. | A/B |

Finally, let’s see how Arithmetic SQL operator is useful in the real world by working out with some examples. Before we start with the demonstration of this SQL operator, we will declare 3 variables out of which 2 variables acts as inputs and 1 will give us the output.

**–Declaring variables to perform Arithmetic operations.**

1 2 3 |
declare @a int,@b int,@c int set @a=40 set @b=20 |

**–Perform Arithmetical operations by using above variables.**

**1.** (+) – For Addition :-

1 2 |
set @c=@a+@b Select @c as addition |

**2.** (-) – For Subtraction :-

1 2 |
set @c=@a-@b Select @c as subtraction |

3. (*) – For Multiplication :-

1 2 |
set @c=@a*@b Select @c as multiplication |

4. (/) – For Division :-

1 2 |
set @c=@a/@b Select @c as division |

**SQL Logic Operators****:-**

This SQL operator is used to compare 2 conditions at a time to determine whether a row can be selected for the output or not.

Operators Name |
Description |

AND | True if all of a set of comparisons are true |

OR | True if either Boolean expression is true |

NOT | Reverse the value of any other Boolean operator |

ALL | True if all of a set of comparisons are true |

ANY | True if any one of a set of comparisons are true |

BETWEEN | True if the operand is within a range |

IN | True if the operand is equal to one of a list of expressions |

LIKE | True if the operand matches pattern |

EXISTS | True if a sub query contains any rows |

IS NULL | See if any nulls are existed in columns |

SOME | True if some of a set of comparisons are true |

**Tips and Tricks :-**

- Operators in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally prevents the query optimizer from using an index to perform a search.
- Exists is much faster than IN, when sub query results is very large.
- IN is faster than Exists when sub query returns small values.
- BETWEEN operator is much more useful than IN operator as Query optimizer can locate a range of numbers much faster (using BETWEEN) than it can find a series of numbers using the IN clause (which is really just another form of the OR clause).
- Always try to position the most expensive AND clause first in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the AND verb in a bottom-up fashion. This means that the first clause (last in the AND
- Always try to position the most expensive OR clause last in the WHERE clause sequencing. Oracle evaluates un-indexed equations, linked by the OR verb in a top-down fashion. This means that the first clause (first in the OR list) is evaluated, and if it is found false, then only second clause is tested