Comparing Expression

Comparing in sql 

=	Equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
<>	Not equal

By using these operators you can restrict rows returned by a query based on variety of condition.

Example 1

Assume you want to retrieve a list of employees who earn monthly salary that is more than $2850.

You will write the below

Select ename,sal

From emp

Where sal >2850;

ENAME             SAL

---------- ----------

JONES            2975

SCOTT            3000

KING              5000

FORD             3000

EXAMPLES FOR COMPARING EXPRETION

EXAMPL 1

Select ename,sal

From emp

Where sal =2850;

ENAME             SAL

---------- ----------

BLAKE            2850

EXAMPL2.

Select ename,sal

From emp

Where sal >=2850;

ENAME             SAL

---------- ----------

JONES            2975

BLAKE            2850

SCOTT            3000

KING             5000

FORD             3000

EXAMPLES FOR COMPARING EXPRETION WITH DATES AND CHARACTER VALUES

1-DATES

SELECT ENAME,HIREDATE

FROM EMP

WHERE HIREDATE<’ 01-JUL-81’;

ENAME      HIREDATE

---------- ---------

SMITH      17-DEC-80

ALLEN      20-FEB-81

WARD       22-FEB-81

JONES      02-APR-81

BLAKE      01-MAY-81

CLARK      09-JUN-81

2-CHRACTER

SELECT DEPTNO,LOC

FROM DEPT

WHERE LOC <> ‘DALLAS’;

  DEPTNO LOC

-------- -------------

  10 NEW YORK

   30 CHICAGO

    40 BOSTON

COMPARTION AND NULL VALUES COMPARE SQL EDITIONS

When you use comparison operators in a where clause ,rowes that contain null result in null ,and are effectively not part of the result a value cannot be equal to ,greater than ,less than, or not equal to Null, because null is not a value.

For example

Assume that you want a list of employees who earn a monthly salary that is greater than or equal to their annual commission. You select ename, Sal, and comm. from the EMP table and add a where clause restricting results to those with a salary greater than or equal to commission.

Check the below cod for more clarification

SELECT ENAME,SAL,COMM

FROM EMP

WHERE SAL >= COMM;

ENAME             SAL       COMM

---------- ---------- ----------

ALLEN              1600         300

WARD             1250         500

TURNER          1500          0