Logical Operators in Oracle SQL

Read this Oracle Tutorial Point point article to know about Logical operators in SQL. The Logical operator combines the results of two components conditions to produce a single result in SQL. The logical operators exists in Oracle are,

• AND
• OR
• NOT

AND Operator:
AND Operator returns TRUE if both or all component conditions are TRUE. AND operator returns FALSE if either is FALSE else returns Unknown.

Truth Table of AND:

AND	TRUE	FALSE	NULL
TRUE	T	F	NULL
FALSE	F	F	F
NULL	NULL	F	NULL

Examples:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE SAL>=1100 AND JOB='CLERK';

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE DNO=10 AND JOB='MANAGER';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE SAL>=1500 AND SAL<5000;

OR Operator:
OR operator returns TRUE if either component conditions is TRUE. OR operator returns FALSE if both are FALSE else returns unknown.

Truth Table of OR:

OR	TRUE	FALSE	NULL
TRUE	T	T	T
FALSE	T	F	NULL
NULL	T	NULL	NULL

Examples:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE SAL >= 1100 OR JOB = 'CLERK';

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP WHERE DNO = 10 OR JOB = 'MANAGER;;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE SAL >= 1200 OR SAL < 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE DNO = 10 OR DNO = 20;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE JOB >= 'CLERK' OR JOB = 'MANAGER';

NOT Operator:
NOT operator returns TRUE is the following condition is FALSE. If the following condition it TRUE, NOT operator return FALSE. If the condition is unknown, NOT operator returns unknown.

Truth Table of NOT:

	TRUE	FALSE	NULL
NOT	F	T	NULL

Combination of AND and OR Operator:

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP 

WHERE (SAL > 1400 OR JOB = 'CLERK') AND DNO = 20;

SQL> SELECT ENO,ENAME,JOB,SAL FROM EMP 

WHERE (DNO = 10 AND JOB = 'MANAGER') OR SAL >= 2000;

Examples:

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT JOB = 'MANAGER';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT SAL > 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT SAL < 6000;

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT HIREDATE = '20-MAY-16';

SQL> SELECT ENAME,SAL,JOB FROM EMP WHERE NOT JOB = 'SALESMAN' AND DNO = 30;

Precedence Rules:
The default precedence order is,

• All comparison operators.
• NOT Operator.
• AND Operator.
• OR Operator.

The precedence is controlled by using parenthesis.