COMPARISON OPERATORS

Using “Comparison operators” we can check weather two expressions are same, except the expressions which are having text, ntext, or image data types.

• Equals: “=”
• Greater than: “>”
• Less than: “<”
• Greater than or equal to: “>=”
• Less than or equal to: “<=”
• Not equal to: “<>” and “!=  (Not ISO standard)”
• Not less than: “!<”
• Not greater than: “!>”

Equals (=): Compares the equality of two expressions

Example:

è Find the employee details from EMP table, which employee is having “106” as employee ID.

Solution:

SELECT * FROM EMP

where ID=106

Greater than (>): Compares two expressions (not null) and returns “True” when the left expression has higher value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary more than 10,000.

Solution:

SELECT * FROM EMP

where Sal>10000

Less than (<): Compares two expressions (not null) and returns “True” when the left expression has lower value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary less than 10,000.

Solution:

SELECT * FROM EMP

where Sal<10000

Greater than or equal to (>=): Compares two expressions (not null) and returns “True” when the left expression has higher or equal value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary more than or equal to 10,000.

Solution:

SELECT * FROM EMP

where Sal>=10000

Less than or equal to (<=): Compares two expressions (not null) and returns “True” when the left expression has lower or equal value than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table, which are getting salary less than or equal to 10,000.

Solution:

SELECT * FROM EMP

where Sal<=10000

Not equal to (<> or !=): Compares two expressions (not null) and returns “True” when the left expression value is not equal to the right expression value. Otherwise the result is “False”.

Example:

è Find the all employee details, except the employees which are getting salary exactly 10,000 from EMP table.

Solution:

SELECT * FROM EMP

where Sal<>10000

OR

SELECT * FROM EMP

where Sal != 10000

Not less than (!<):Compares two expressions (not null) and returns “True” when the left expression does not have a value lower than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table; their salary should not less than 10,000.

Solution:

SELECT * FROM EMP

where Sal !< 10000

Not greater than (!>): Compares two expressions (not null) and returns “True” when the left expression does not have value higher than right expression. Otherwise the result is “False”.

Example:

è Find the employee details from EMP table; their salary should not greater than 10,000.

Solution:

SELECT * FROM EMP

where Sal !> 10000