Logical operators

Logical operators in SQL

And	Used to define conditions that must all be true for a row to be retrieved.
OR	Used to define two conditions of which only one must be true for a row to be retrieved.
NOT	You can use the NOT operator to invert the result of single condition in the where clause.

Examples.

AND OPERATOR

Suppose you want to display the employee number and name and salary of all employees

Who earn equal and more than 800$ and who are in department 20. Then you will write the next statement

Select empno,ename,deptno,sal

 From emp

 Where sal >=800

 And deptno=20;

 EMPNO ENAME          DEPTNO        SAL

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

  7369 SMITH              20                 800

  7566 JONES              20                 2975

  7788 SCOTT              20                 3000

  7876 ADAMS           20                  1100

  7902 FORD               20                  3000

OR Operator

Assume that  want to retrieve information about employees who earn a salary greater than or equal to $1250 or who have the job title SALESMAN

Then you can combine these conditions in a where clause by using the or operator.

SELECT ENAME,JOB,SAL

FROM EMP

WHERE SAL >= 1250

OR JOB =’ SALESMAN’;

ENAME      JOB                    SAL

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

ALLEN          SALESMAN          1600

WARD          SALESMAN           1250

JONES          MANAGER            2975

MARTIN       SALESMAN          1250

BLAKE          MANAGER           2850

CLARK         MANAGER            2450

SCOTT         ANALYST             3000

KING            PRESIDENT           5000

TURNER      SALESMAN           1500

FORD          ANALYST              3000

MILLER        CLERK                  1300

Notice that you can use the OR logical operator in combination with any comparison operator to specify conditions in a where clause.

NOT operator

You can use the NOT logical operator in combination with the comparison operators IN, BETWWEN, LIKE, AND IS NULL.

To retrieve a list of employees who are not salesman, president, clerk

You can use the not operator IN combination with the in operator.

 SQL> SELECT EMPNO,ENAME,JOB,SAL

   FROM EMP

    WHERE JOB NOT IN ('SALESMAN', 'PRESIDENT', 'CLERK');

     EMPNO ENAME      JOB              SAL

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

    7566 JONES     MANAGER           2975

    7698 BLAKE    MANAGER           2850

    7782 CLARK   MANAGER           2450

    7788 SCOTT   ANALYST            3000

    7902 FORD     ANALYST            3000

You place NOT directly before the IN,BETWEEN,and LIKE operators in a sql statement.

However with the IS NULL operator ,you place the not keyword in the middle to produce the operator IS NOT NULL.

EXAMPL

SELECT ENAME,SAL,JOB

FROM EMP

WHERE  JOB NOT LIKE 'SALESMAN';

ENAME             SAL JOB

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

SMITH             800 CLERK

JONES            2975 MANAGER

BLAKE            2850 MANAGER

CLARK            2450 MANAGER

SCOTT            3000 ANALYST

KING             5000 PRESIDENT

ADAMS            1100 CLERK

JAMES             950 CLERK

FORD             3000 ANALYST

MILLER           1300 CLERK

EXAMPL: for NOT operator with NULL VALUES.

SELECT ENAME,MGR

FROM EMP

WHERE MGR IS NOT NULL;

ENAME             MGR

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

SMITH            7902

ALLEN            7698

WARD             7698

JONES            7839

MARTIN           7698

BLAKE             7839

CLARK             7839

SCOTT              7566

TURNER           7698

ADAMS            7788

JAMES              7698

ENAME             MGR

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

FORD               7566

MILLER           7782

13 rows selected.