Conjunctive Operators


Team-Fly

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents

	Hour 8. Using Operators to Categorize Data

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.


Team-Fly

Top