Where clause must have one condition statement in Select statement. To restrict depending upon condition minimum requirement is an operator in transact SQL.
Contents
Comparison Operators:
These operator are used for comparison the two value in SQL statement.
- Relation Operator
- Logical Operator
- Arithmetic Operator
Please read the previous post
Introduction to Structured Query Language (SQL)
Object-Relational Database
SELECT Statement in SQL
Group By Clause in SQL
Having Clause in SQL
Relation Operator :
Please look into the below table and it contains all the relational operator in SQL
| Operator | Times of use |
|---|---|
| = (Equals) | Equal to |
| > (Greater Than) | Greater than |
| < (Less Than) | Less than |
| >= (Greater Than or Equal To) | Greater than or equal to |
| <= (Less Than or Equal To) | Less than or equal to |
| != (Not Equal To) | Not Equal To |
| <> (Not Equal To) | Not equal to (not ISO standard) |
| !> (Not Greater Than) | Not greater than (not ISO standard) |
| !< (Not Less Than) | Not less than (not ISO standard) |
Question : Write a query, which will fetch data from employee table and those employee’s salary should be greater than 20000
SELECT * FROM employee
where salary > 20000
Question : Write a query, which will fetch data from employee table and those employee’s salary should not be greater than 20000
SELECT * FROM employee
where salary !< 20000
Logical Operator:
The below operator are belong to logical operator in SQL
| Operator | Times of use |
|---|---|
| OR | The row to be selected at least one of the conditions must be true. |
| AND | A row to be selected all the specified conditions must be true. |
| NOT | A row to be selected the specified condition must be false. |
Arithmetic Operator :
The below operator are belong to logical Arithmetic in SQL
| Operator | Times of use |
|---|---|
| + (Add) | Addition |
| – (Subtract) | Subtraction |
| * (Multiply) | Multiplication |
| / (Division) | Division |
| % (Modulo) | Returns the integer remainder of a division. |
Operator in Transact SQL
Now we will discuss about some special operator in SQL such as Null,Wildcards, Like, IN etc.
Null Operator :
It is a very special operator in SQL servers. The NULL operator is used to compare a value with a NULL value.
“It is a special type of value which is unassigned or inapplicable”. To extract data depending on NULL value user can use is NULL operator.
Select * from employee
where comm is NULL;
The above statement only returns those many employees who are not getting commission from the company.
NOT Operator:
It is used for negative statement in SQL query. The NOT operator reverses the meaning of the logical operator with which it is used. Let’s see the below query we will understand properly.
Select * from employee
where comm IS NOT NULL;
The above statement only returns those many employees who are getting commission from the company.
EXISTS Operator:
The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria. Most of the time we are using if-else block inside store procedure and PL/SQL.
IF EXISTS (Select 1 from employee where name like '%N%')
Block 1
ELSE
Block2
IN Operator:
The IN operator is used to compare a value to a list of literal values that have been specified.
This operator can be operated on any type of value.
Select * from employee
where Location IN ('Bangalore', 'Bhubaneswar', 'Mumbai');
The above statement only returns those many employees who are working under Bangalore or Bhubaneswar or Mumbai.
BETWEEN Operator:
The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value. This is an operator responsible to display the output between a given range of values.
This operator can be operated a two type of values. that is number and date.
Select * from employee
where salary between 30000 and 70000;
The above statement only returns those many employees who are getting salary between 30000 and 70000.
Reference Link
