Defining Conditions Using the Logical Operators

Topic Introduction: A logical condition combines the result of two component conditions to produce a single result based on those conditions or it inverts the result of a single condition. A row is returned only if the overall result of the condition is true.

Three logical operators are available in SQL:

AND: Returns TRUE if both component conditions are true

OR: Returns TRUE if either component condition is true

NOT: Returns TRUE if the condition is false

All the examples so far have specified only one condition in the WHERE clause. We can use several

conditions in a single WHERE clause using the AND and OR operators.

Example (AND):

SELECT employee_id, first_name||' '||last_name "Employee Name", job_id, salary, commission_pct

FROM employees

WHERE salary >= 10000

AND job_id LIKE '%MAN%';

Explanation: In this example, we have given the use of AND operator. we know using the AND operator requires both the component conditions to be true. 

Here both the component conditions must be true for any record to be selected. Therefore,

only those employees who have a job title that contains the string ‘MAN’ and earn 10,000 or more

are selected.

Example (OR):

SELECT employee_id, first_name||' '||last_name "Employee Name", job_id, salary, commission_pct

FROM employees

WHERE salary >= 10000

OR job_id LIKE '%MAN%'

Explanation: In the example, either component condition can be true for any record to be selected. Therefore, any employee who has a job ID that contains the string ‘MAN’ or earns $10,000 or more is selected.

Example (NOT):

SELECT employee_id, first_name||' '||last_name "Employee Name", job_id, salary, commission_pct

FROM employees

WHERE  job_id NOT IN ('%MAN%')

Explanation: In the example, either component condition can be true for any record to be selected. Therefore, any employee who has a job ID that not contains the string ‘MAN’  is selected.