«Back to Home

Oracle Jump Start
Topics

How To Use Comparison Operators In Oracle

Description
 
Comparison operators are used in where clause. Comparison operator is represented by special character or keywords. The data items are called the operands or the arguments.
Comparison operator gives the result value True, False, Null or 0, 1.
 
 Comparison  Operator Description
 = Equal
 !=  Not Equal
 <> Not Equal
 > Greater Than
 >=  Greater Than Equal To
 <  Less Than
 <= Less Than Equal TO
Equal Operator
 
Equal operator is represented by =
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id = 7001;  
1
 
Not Equal Operator
 
Not Equal Operator is represented by !=
 
Syntax 
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id != 7002;  
2 
 
Not Equal Operator
 
Not equal operator is represented by <>
Both the operators <> or != gives the same results.
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id <> 7002;  
3 
 
Greater Than Operator
 
Greater than operator is represented by <
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id > 7003;  
4 
 
Greater Than Equal To Operator
 
Greater than equal to operator is represented by >=
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id >= 7003;  
5 
 
Less Than Operator
 
Less Than Operator is represented by <
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id < 7003;  
6 
 
Less Than Equal To Operator
 
Less Than Equal To Operator is represented by <=
 
Syntax
  1. Select * from table_name  
  2. Where condition;  
Example
  1. Select * from Employee  
  2. where Emp_Id <= 7003;  
7 
 
Advanced Operators
 
 Comparison Operator Description
 IN ()  For matching value in a list.
 Not  For Negates Operand
 Between It represents the range
 Is Null Null Value
 Is Not Null  Non-Null Values
 Like  Matching the pattern

IN () Operator
 
Expression
 
Expression IN(value1, value2 ….. value_n)
 
Syntax
  1. Select * from Table_name  
  2. Where IN Condition;  
Example
 
This example uses the character value.
  1. Select *  
  2. From Employee  
  3. Where Emp_name=’Smith’;  
8 
 
IN Operator Example with Numeric
 
Syntax
  1. Select * from  
  2. table_name  
  3. Where IN Condition; 
Example
  1. Select * from Employee  
  2. Where Emp_id IN(7001, 7002,7003);  
  3. Example using Not Operator  
9 
 
Syntax
  1. Select * from  
  2. table_name  
  3. Where NOT IN Condition;  
Example
  1. Select * from Employee  
  2. Where Emp_id NOT IN(7001, 7002,7003);  
10 
 
Between Operator

Expression
 
Expression Between value 1 and value 2;
 
Syntax
  1. Select * From table_name  
  2. Where Between value1 and value2;  
Example
  1. Select * From Employee  
  2. Where Emp_id Between 7004 and 7008;  
11 
 
IS Null Operator
 
Expression
 
Expression is Null
 
Syntax
  1. Select * From table_name  
  2. Where Column_name is Null;  
Example 
  1. Select * From Employee  
  2. Where Emp_id is Null;  
12 
 
IS NOT Null Operator
 
Expression
 
Expression Is not Null
 
Syntax
  1. Select * From table_name  
  2. Where Column_name is Not Null;  
Example
  1. Select * From Employee  
  2. Where Emp_id is Not Null;  
13
 
Like Operator
 
Expression
 
Expression Like Pattern[‘escape character’]
 
Syntax
  1. Select Column_name  
  2. From table_name  
  3. where Column_name Like ‘ ’;  
Example 
  1. Select Emp_name  
  2. From Employee  
  3. where Emp_name Like ‘Ki_g’;  
14 
 
Summary
 
Thus, we learnt, Comparison operators are used in where clause. Comparison operator is represented by a special character or keywords. Comparison operator gives the result value True, False, Null or 0, 1. We learned, how to use these operators with examples.