Comparison Operators

Comparison Operators

Comparison operators are used to compare two or more values. They are usually found in the WHERE clause of a SELECT statement, though you may use them in any valid SQL expression.

The usage is identical across all three databases except for the nonstandard operators !< and !> — they are recognized by IBM DB2 UDB 8.1 and MS SQL Server 2000, but are excluded from Oracle 9i. The nonstandard not equal to operator, !=, could be used in all three dialects. Table 11-5 lists all comparison operators.

To illustrate the uses of the operators listed in Table 11-5, we're going to query the ACME database view V_CUSTOMER_TOTALS.

To restrict the number of rows returned by a query SQL uses the WHERE clause, discussed in Chapter 7. Comparison operators logically fit into this strategy, allowing you to limit the number of rows based on some search criteria. Consider the query that returns all rows. (We are showing only five of these here.)

SELECT customer_name,
		order_number, total_price FROM v_customer_totals customer_name order_number
		total_price ----------------- ----------- ----------- WILE BESS COMPANY 523720
		7538.20 WILE BESS COMPANY 523723 11186.00 WILE BESS COMPANY 523724 8745.50 WILE
		ELECTROMUSICAL INC. 523727 6630.40 WILE ELECTROMUSICAL INC. 523728
		6630.40

Someone who wanted to see, for instance, only the records for WILE BESS COMPANY would issue the following query:

SELECT customer_name,
		order_number, total_price FROM v_customer_totals WHERE customer_name = 'WILE
		BESS COMPANY' customer_name order_number total_price -----------------
		------------ ----------- WILE BESS COMPANY 523720 7538.20 WILE BESS COMPANY
		523723 11186.00 WILE BESS COMPANy 523724 8745.50

This query would go through all customers in the view, and by comparing the value in the column CUSTOMER_NAME with the one supplied in the WHERE clause it would select only those that match WILE BESS COMPANY, excluding all others.

To find out only the records that have TOTAL_PRICE over a certain preset limit (say, $7,500), one would use the greater than operator (>):

SELECT customer_name,
		order_number, total_price FROM v_customer_totals WHERE total_price > 7500
		customer_name order_number total_price ----------------- -----------
		----------- WILE BESS COMPANY 523720 7538.20 WILE BESS COMPANY 523723 11186.00
		WILE BESS COMPANy 523724 8745.50

As you can see, this effectively excluded all records where the total price was less than $7,500. If you wish to include a cut-off value, operators greater than or equal to (>=) and/or less than or equal to (<=) might be used:

SELECT customer_name,
		order_number, total_price FROM v_customer_totals WHERE total_price <=
		7538.20 customer_name order_number total_price -------------------------
		----------- ----------- WILE BESS COMPANY 523720 7538.20 WILE ELECTROMUSICAL
		INC. 523727 6630.40 WILE ELECTROMUSICAL INC. 523728 6630.40

This query returns records from the V_CUSTOMER_TOTALS view that are equal to $7538.20 or less than this value.

To find out the orders that were placed by any of your customers but one, you would use not equal to operators — <> or != — the latter being a nonstandard operator, supported nevertheless by all three vendors:

SELECT customer_name,
		order_number, total_price FROM v_customer_totals WHERE customer_name <>
		'WILE BESS COMPANY' customer_name order_number total_price
		------------------------ ------------ ----------- WILE ELECTROMUSICAL INC.
		523727 6630.40 WILE ELECTROMUSICAL INC. 523728 6630.40

We have mentioned earlier that the WHERE clause is not the only place where you can use comparison operators. Chapter 10 introduced the CASE expression, which we're going to use here:

SELECT order_number, total_price
		CASE WHEN total_price < 7500 THEN 'medium order' WHEN total_price > 7500
		AND total_price < 10000 THEN 'big order' WHEN total_price > 10000 THEN
		'very big order' ELSE 'cannot say' END FROM v_customer_totals order_number
		total_price ----------- ----------- --------------- 523720 7538.20 big order
		523723 11186.00 very big order 523724 8745.50 big order 523727 6630.40 medium
		order 523728 6630.40 medium order.