Using the AND/OR Logical Operators in SQL

Always remember the following rules for the AND and OR logical operators:

·         AND will return rows only when both conditions are TRUE.

·         OR requires either condition to be TRUE.

·         AND has a higher precedence over OR.

Note that both these operators can be used together in the WHERE clause of a SQL statement to construct compound logical expression.

The WHERE clause in the following example is made up of two conditions, and the keyword AND is used to join them. AND instructs the database management system software to return only rows that meet all the conditions specified. If a record has department number 20, but the job id is not MK_MAN (Marketing Manager), it is not retrieved. Similarly, records having job id MK_MAN in other departments will not to be retrieved as well.

In the first example below, the AND operator is used to search employees working in department number 20 as MK_MAN. As you can see, the sole fetched record fulfills both conditions - mentioned before and after the AND operator. The example contains a single AND clause and is thus made up of two filter conditions. To narrow the result, you are allowed to add more filter conditions, each separated by an AND keyword like this: WHERE department_id=20 AND job_id=’MK_MAN’ AND first_name=’Michael’

    SQL Statement :

SELECT       first_name, department_id, job_id

FROM          employees

WHERE       department_id=20 AND job_id=’MK_MAN’;

The second logical operator that you can use in you SQL statement is the OR operator which is less restrictive and thus returns more rows. Most database management systems do not even evaluate the second condition in an OR WHERE clause if the first condition has already been met i.e. the rows are returned without considering the second condition if the first condition evaluates to true.

The query mentioned below, fetches all employees who are either in department number 20 OR who work as MK_MAN. The second record returned by the query doesn’t fulfills the job id part of the condition, but since it satisfies the first condition (department_id =20), it is picked by the query for display. 

    SQL Statement :

SELECT     first_name, department_id, job_id

FROM        employees

WHERE     department_id=20 OR job_id=’MK_MAN’;

    Output :