Conjunctive Operators | What if you want to use multiple conditions to narrow data in a SQL statement? You must be able to combine the conditions, and you would do this with what is call conjunctive operators. These operators are | These operators provide a means to make multiple comparisons with different operators in the same SQL statement. The following sections describe each operator's behavior. AND The AND operator allows the existence of multiple conditions in a SQL statement's WHERE clause. For an action to be taken by the SQL statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE. Example | Meaning | WHERE EMPLOYEE_ID = '333333333' AND SALARY = '20000' | The EMPLOYEE_ID must match 333333333 and the SALARY must equal 20000 | SELECT * FROM PRODUCTS_TBL WHERE COST > 10 AND COST < 30; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 90 LIGHTED LANTERNS 14.5 2 rows selected. In this output, the value for cost had to be both greater than 10 and less than 30 for data to be retrieved. SELECT * FROM PRODUCTS_TBL WHERE PROD_ID = '7725' AND PROD_ID = '2345'; no rows selected This statement retrieved no data because each row of data has only one product identification. OR The OR operator is used to combine multiple conditions in a SQL statement's WHERE clause. For an action to be taken by the SQL statement, whether it is a transaction or query, at least one of the conditions that are separated by OR must be TRUE. Example | Meaning | WHERE SALARY = '20000' OR SALARY = '30000' | The SALARY must match either 20000 or 30000 | | Each of the comparison and logical operators can be used singularly or in combination with each other. | SELECT * FROM PRODUCTS_TBL WHERE PROD_ID = '7725' OR PROD_ID = '2345'; PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 2345 OAK BOOKSHELF 59.99 1 rows selected. In this output, either one of the conditions had to be TRUE for data to be retrieved. Two records that met either one or the other condition were found. | When using multiple conditions and operators in a SQL statement, you may find that it improves overall readability if parentheses are used to separate statements into logical groups. However, be aware that the misuse of parentheses could adversely affect your output results. | In the next example, notice the use of the AND and two OR operators. In addition, notice the logical placement of the parentheses to make the statement more readable. SELECT * FROM PRODUCTS_TBL WHERE COST > 10 AND (PROD_ID = '222' OR PROD_ID = '90' OR PROD_ID = '11235'); PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 90 LIGHTED LANTERNS 14.5 2 rows selected. The cost in this output had to be greater than 10, and the product identification had to be any one of the three listed. A row was not returned for PROD_ID 222 because the cost for this identification was not greater than 10. |