Objective The article explains the use of Logical operators (AND Condition, OR Condition and NOT Condition) in clubbing the conditions in a SELECT statement. 1. Introduction to Logical operators To ensure proper and enhanced data refining of query result set, multiple filter conditions can be appended to WHERE clause in a single SELECT statement using Logical operators. Refer the truth table for each of the operator. AND OR NOT The records which satisfy all the participating conditions qualify to appear in final result set. Code (SQL): …WHERE col1 = ‘VALUE’ ----------//Condition 1 AND col3 <> ‘CLUB-ORACLE’ ----------//Condition 2 AND (col2 = 1000 OR col4 > 5000) ----------//Condition 3 & 4 (later covered in Precedence section) For instance, in the below example, a row appears from table in the result set only, if condition 1 and 2 are satisfied and either of conditions return true in 3 & 4. 1.1. AND Operator With the use of AND Boolean operator, we make sure that all the participating terms are TRUE (satisfied). Example 1.1.1 Below SQL selects all HR's from department 20 Code (SQL): SELECT EMPLOYEE_ID, ENAME, SALARY FROM EMPLOYEE WHERE DEARTMENT_ID = 20 AND JOB_ID = 'HR' EMPLOYEE_ID ENAME SALARY --------------- --------------- --------------- 120 KATE 3400 140 NEO 11000 170 RYAN 5600 1.2. OR Operator OR operator sets to TRUE if either (any one) of the participating terms is TRUE (satisfied). Example 1.2.1 Below SQL selects either Managers or those employee whose salary exceeds 10000. Code (SQL): SELECT EMPLOYEE_ID, ENAME, SALARY, JOB_ID FROM EMPLOYEE WHERE JOB_ID = 'MGR' OR SALARY > 10000 EMPLOYEE_ID ENAME SALARY JOB_ID --------------- --------------- --------------- --------------- 100 KING 7500 MGR 110 TIM 12500 DEV 150 JOHN 7800 MGR 180 PINNE 10500 MGR 1.3. NOT Operator The NOT operator just negates or reverses the logical stand of a condition. Note: The NOT operator can also be used with other SQL operators, such as BETWEEN, LIKE, and NULL. For example, Code (Text): a. WHERE JOB_ID NOT IN ('AC_ACCOUNT', 'AD_VP') b. WHERE SALARY NOT BETWEEN 10000 AND 15000 c. WHERE LAST_NAME NOT LIKE '%A%' 2. Rules of Precedence of Logical operators Since multiple logical operators can be implemented in the same SQL query, it is equally important to set a protocol of their evaluation. Refer the below table for precedence rules. However, you can override the default order by using parentheses [()] around the expressions that you want to calculate first. Illustrations Example 2.1. Below example displays the employee details whose department id is 10 and last name is KING Code (SQL): SELECT ENAME, SALARY FROM EMPLOYEE WHERE DEPARTMENT_ID = 10 AND LAST_NAME = ‘KING’ Example 2.2. Below SQL displays the employee details whose department id is 10 and last name is KING, or whose JOB ID is Manager Code (SQL): SELECT ENAME, SALARY FROM EMPLOYEE WHERE DEPARTMENT_ID = 10 AND LAST_NAME = ‘KING’ OR JOB_ID = ‘MGR’ As per the precedence rules, above query contains two filters. Firstly, the employees which are working in department 10 and last name as KING and secondly, the employees which have job role as MANAGER irrespective of working department. Now, the final result would be the union of these two queries. Example 2.3. Below query will check only in department 10 for the employees whose last name is KING or job role is Manager. Code (SQL): SELECT ENAME, SALARY FROM EMPLOYEE WHERE DEPARTMENT_ID = 10 AND (LAST_NAME = ‘KING’ OR JOB_ID = ‘MGR’) References Any External / Internal references for the article / tutorials /code snippets.