Logical Operators

ALL

Compares a scalar value with a single-column set of values. It is used in conjunction with comparison operators and is sometimes classified as a comparison operator. It returns TRUE when specified condition is TRUE for all pairs; otherwise it returns FALSE. The example of its usage is given in Chapter 8, the section "Using Subqueries in a WHERE clause."

ANY | SOME

Compares a scalar value with a single-column set of values. The keywords ANY and SOME are completely interchangeable. The operator returns TRUE if specified condition is valid for any pair; otherwise it returns FALSE. The example of its usage is given in Chapter 8, the section "Using Subqueries in a WHERE clause."

BETWEEN <expression> AND <expression>

The BETWEEN operator allows for "approximate" matching of the selection criteria. It returns TRUE if the expression evaluates to be greater or equal to the value of the start expression, and is less or equal to the value of the end expression. Used with negation operator NOT, the expression evaluates to TRUE only when its value is less than that of the start expression, or greater than the value of the end expression.

The following query retrieves data about product ID, product description and product price from the PRODUCT table, where product price is in the range between 15 and 25 dollars.

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_price_n BETWEEN 15 AND 25 prod_id_n description price ---------
		  ------------------------ ----- 990 SPRUCE LUMBER 30X40X50 18.24 3045 STOOL CAPS
		  9'' 15.92 4055 GAZEBOS 40X30X60 16.03 4761 BAR
		  RAILS 24X48X128 23.10 4964 BASES 30X45X60 23.10 5786 CRATING MATERIAL 12X48X72
		  17.90 (6 row(s) affected)

Note that the border values are included into the final result set. The operator works identically across all three databases and could be used with a number of different data types: dates, numbers, and strings.

Though rules for evaluation strings are the same, the produced results are not that straightforward as those with the numbers. The string are evaluated according to the characters in the value, and unless full string is specified, the border limit values are not included. For example, if one wants to get the product's information for a range of descriptions starting with "C" and "S," the following query could be used:

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_description_s BETWEEN 'C' AND 'S' prod_id_n description price ---------
		  ---------------------------- ----- 4000 HAND RAILS ROUNDS 48X48X12 11.80 4055
		  GAZEBOS 40X30X60 16.03 5786 CRATING MATERIAL 12X48X72 17.90 (3 row(s)
		  affected)

Note that the product with description 'SPRUCE LUMBER 30X40X50' was not included in spite that it does starts with 'S'; the cut-off criterion 'S' implies that only description consisting of a single 'S' answers the condition; using wildcard characters (see later in the chapter) does not help in this case. To include the product for the above description, the full value must be used, or the first TWO characters of the value next in line (see the query above), and so on

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_description_s BETWEEN 'C' AND 'ST' prod_id_n prod_description_s
		  prod_price_n --------- ------------------------------- ------------ 990 SPRUCE
		  LUMBER 30X40X50 18.24 4000 HAND
		  RAILS ROUNDS 48X48X12 11.80 4055 GAZEBOS 40X30X60 16.03 5786 CRATING MATERIAL
		  12X48X72 17.90 (4 row(s) affected)

IN

This operator matches any given value to that on the list — either represented by literals, or returned in subquery. The following query illustrates the concept of the IN operator

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_price_n IN (10,15,18.24,16.03) prod_id_n description price ---------
		  ------------------------ ----- 990 SPRUCE LUMBER 30X40X50 18.24 4055 GAZEBOS
		  40X30X60 16.03 (2 row(s) affected)

Since we do not have products priced exactly at 10 or 15 dollars, only two matching records were returned.

The operator IN behavior could be emulated (to a certain extent) by using OR operator. The following query would bring the result set identical to that returned by the query using a list of literals

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_price_n = 10 OR prod_price_n = 15 OR
		  prod_price_n = 18.24 OR prod_price_n = 16.03 prod_id_n description price
		  --------- ------------------------ ----- 990 SPRUCE LUMBER 30X40X50 18.24 4055
		  GAZEBOS 40X30X60 16.03 (2 row(s) affected)

The values on the IN list could be generated dynamically from a subquery. The following query retrieves all descriptions for the products that were sold in quantities of less than 90 items:

SELECT prod_description_s FROM
		  product WHERE prod_id_n IN (SELECT ordline_prodid_fn FROM order_line WHERE
		  ordline_ordqty_n < 90) prod_description_s
		  -------------------------------------------- HAND RAILS ROUNDS 48X48X12 BAR
		  RAILS 24X48X128 BAR RAILS 30X45X60 BASES 30X45X60 CRATING MATERIAL 12X48X72 (5
		  row(s) affected)

Using NOT operator in conjunction with IN would return all records that are not within the specified list of values — either predefined or generated from a subquery.

EXISTS

The EXISTS operator checks for the existence of any rows with matched values in the subquery. The subquery could query the same table, or different table(s), or a combination of both (see Chapter 8 for information on correlated query). The operator acts identically in all three RDBMS implementations

EXISTS usage resembles that of IN operator (normally used with correlated query, discussed in Chapter 8). The following SQL query produces results identical to those produced by the queries in the above examples:

SELECT prod_description_s FROM
		  product WHERE EXISTS(SELECT * FROM order_line WHERE ordline_prodid_fn =
		  product.prod_id_n AND ordline_ordqty_n < 90) prod_description_s
		  -------------------------------------------- HAND RAILS ROUNDS 48X48X12 BAR
		  RAILS 24X48X128 BAR RAILS 30X45X60 BASES 30X45X60 CRATING MATERIAL 12X48X72 (5
		  row(s) affected)

The use of an additional condition (ordline_prodid_fn = product.prod_id_n) is necessary to limit output to those records from PRODUCT to only those that have corresponding records in ORDER_LINE table – i.e., only those products for which there are orders. Without this condition, the query would bring all products from the PRODUCTS table.

SELECT prod_description_s
			 FROM product WHERE EXISTS (SELECT * FROM DUAL)

The EXISTS operator produces results identical to '= ANY' from the examples in the respective sections. Using operator NOT in conjunction with EXISTS results in records when no rows are returned by a subquery.

LIKE

Operator LIKE belongs to the "fuzzy logic" domain. It is used any time when criteria in the WHERE clause of the SELECT query are only partially known. It utilizes a variety of wildcard characters to specify the missing parts of the value (Table 11-3). The pattern must follow the LIKE keyword.

The following query requests information from the table CUSTOMER of the ACME database, where customer name (field CUST_NAME_S) starts with 'WILE'

SELECT
		  cust_id_n, cust_name_s FROM customer WHERE cust_name_s LIKE 'WILE%' cust_id_n
		  cust_name_s --------- -------------------------------- 152 WILE BESS COMPANY 55
		  WILE ELECTROMATIC INC. 63 WILE ELECTROMUSICAL INC. 7 WILE ELECTRONICS INC. 1
		  WILE SEAL CORP. (5 row(s) affected)

Note that blank spaces are considered to be characters for the purpose of the search.

If, for example, we need to refine a search to find a company whose name starts with WILE and has a second part sounding like EAL ("MEAL"? "SEAL"?), the following query would help:

SELECT
		  cust_id_n, cust_name_s FROM customer WHERE
		  cust_name_s LIKE 'WILE% _EAL%' cust_id_n cust_name_s ---------
		  ------------------------------ 1 WILE SEAL CORP. (1 row(s)
		  affected)

In plane English, this query translates as "all records from the table CUSTOMER where field CUST_NAME_S contains the following sequence of characters: the value starts with WILE followed by unspecified number of characters, then blank space, and the second part of the value starts with some letter or number followed by combination EAL; the rest of the characters is unspecified".

In Microsoft SQL Server (and Sybase, as well), you also may use matching pattern that specifies range of characters. The following query retrieves records for the customer whose second part of the name starts with either 'S' or 'B':

SELECT
		  cust_id_n, cust_name_s FROM customer WHERE cust_name_s LIKE 'WILE% [S,B]%'
		  cust_id_n cust_name_s --------- ------------------------------------ 152 WILE
		  BESS COMPANY 1 WILE SEAL CORP. (2 row(s) affected)

ESCAPE clause in conjunction with the LIKE operator allows for inclusion wildcard characters themselves to be included in the search string. It allows you to specify an escape character to be used to identify special characters within the search string that should be treated as "regular" ones. Virtually any character could be designated as an escape character in a query, though caution must be exercised in order not to use characters that might be encountered in the values themselves (e.g., use of the '%' or 'L' as an escape character would result in erroneous results to be returned). The clause is supported by all three major databases and is part of SQL standard.

The following example uses an underscore sign (_) as one of the search characters; it queries INFORMATION_SCHEMA view (Microsoft SQL Server 2000 specific view for accessing information about objects present in the current database; covered in detail in Chapter 13):

SELECT table_name, table_type
		  FROM INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'ORD%/_L%' ESCAPE '/'
		  table_name table_type --------------- ---------- ORDER_LINE BASE TABLE (1
		  row(s) affected)

The query requests records from the view where table name starts with 'ORD' followed by unspecified number of characters, has an underscore "_" as part of its name, followed by 'L' and, again, ending with an unspecified number of characters. Since the underscore character has a special meaning as a wildcard character it has to be preceded by an escape character '/'. As you can see, the table name ORDER_LINE uniquely fits these requirements.

With a bit of practice one could construct quite sophisticated pattern matching queries. Here is an example: the query that specifies exactly three characters preceding 'E' in the first part of the name, followed by unspecified number of characters, exactly one character preceding letters 'ES' in the second part, followed by unspecified number of characters

SELECT
		  cust_id_n, cust_name_s FROM customer WHERE cust_name_s LIKE '___E% _ES%'
		  cust_id_n cust_name_s --------- ----------------------------------------- 89
		  INTEGRATED POWER DESIGNS 152 WILE BESS COMPANY (2 row(s) affected)

The results might be surprising at first glance: why does the resultset include 'INTEGRATED POWER DESIGNS'? To understand the results one should recall that '%' stands for any character, blank spaces included; therefore 'GRATED POWER' string fits the criteria; it is followed by a blank space and a word that includes 'ES' as the second and first characters.

AND

AND combines two Boolean expressions and returns TRUE when both expressions are TRUE. The following query returns records for the product with a unit price over $20 and whose description starts with 'S':

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product WHERE
		  prod_price_n > 20 AND prod_description_s LIKE 'S%' prod_id_n description
		  price --------- ------------------------ ----- 1880 STEEL NAILS 6'' 33.28 2871
		  STOOL CAPS 5'' 26.82 (2 row(s) affected)

Only records that answer both criteria are selected.

When more than one logical operator is used in a statement, AND operators are evaluated first. The order of evaluation could be changed through use of parentheses.

NOT

This operator negates a Boolean input. It could be used to reverse output of any other logical operator discussed so far in this chapter. Here is a simple example using IN operator.

SELECT prod_id_n,
		  prod_description_s description, prod_price_n price FROM product
		  
		  WHERE prod_price_n NOT IN (10,15,18.24,16.03) prod_id_n description price
		  --------- ----------------------------- ----- 1880 STEEL NAILS 6'' 33.28 2871
		  STOOL CAPS 5'' 26.82 3045 STOOL CAPS 9'' 15.92 4000 HAND RAILS ROUNDS 48X48X12
		  11.80 4761 BAR RAILS 24X48X128 23.10 4906 BAR RAILS 30X45X60 27.00 4964 BASES
		  30X45X60 23.10 5786 CRATING MATERIAL 12X48X72 17.90 (8 row(s)
		  affected)

The query returned information for the products whose price does not match any on the supplied list, i.e., where operator IN returns TRUE (match) it becomes FALSE, while FALSE (no match) translates into TRUE.

OR

Combines two conditions according to the rules of Boolean logic (see Appendix L for more information on Boolean logic). When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses. The example of the usage of the OR operator is given earlier in the chapter, in a paragraph discussing operator IN.