Network Computingwww.networkcomputing.com

There are several logical operators in SQL. The operators are used to perform logical comparisons between groups of search conditions. If you have never worked in a programming language before then much of this may be new to you.

Boolean logic is a system of ascertaining the truth or falsity of sets of sentences, given certain relationships between those sentences. The main logical operators for Boolean statements are AND, OR, and NOT. In the context of programming languages, Boolean logic can be confusing at first glance, since it is not always obvious how we should apply the various logical operators - specifically, it is not always obvious what internal order of operations within a statement should have.

We will be looking at ways to make queries minimally complicated and easy to read.

SQL supports the following logical operators:

Click here to enlarge

George Bush IS President AND Tony Blair IS Prime Minister - True

Al Gore IS President AND Tony Blair IS Prime Minister - False

Al Gore IS President OR Tony Blair IS Prime Minister - True

Al Gore NOT President and Tony Blair IS Prime Minister - True

WHERE
Manufacturer = 'BobCo'
OR Manufacturer = 'JamesInc'
AND Weight < 10

This may appear to be correct, but the order of evaluation, or precedence, in SQL could catch us out here. The way that SQL evaluates operators is in the following order, that is it evaluates those functions in step one before those in step two, and so on:

4. + (Positive), - (Negative), ~ (Bitwise NOT)

5. *(Multiply), / (Division), % (Modulo)

6. + (Add), + (Concatenate), - (Subtract)

7. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

8. ^ (Bitwise Exlusive OR), & (Bitwise AND), | (Bitwise OR)

9. NOT

10. AND

11. ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

12. = (Assignment)

This list may be a bit confusing, but here is the way that it works. We work from left to right on the statement. It helps at this point to dust off the cobwebs and remember the old algebra. If we have the following:

(3 * 2 + 6 / 2) <= (4 / 2 + 6 * 1)

We would evaluate using the above rules. Order number 2 would be first (multiplication and division):

(6 + 3) <= (2 + 6)

We would then use order number 3 to add and subtract:

9 <= 8

Finally, order 4 would be used to perform the logical evaluation. This would obviously evaluate to FALSE since 9 is not less than or equal to 8.

It is the same with SQL statements. Just follow the order of operations above from left to right and this is how SQL will evaluate a function.

So, we can see that the AND operator gets evaluated before the OR operator. Therefore, our WHERE clause is actually saying that we are looking for parts that weigh less than 10 pounds that are made by JamesInc and all parts made by BobCo. We could rewire the order to correct this, but this would be tedious and error prone. Instead, we can use an algebraic grouping. This is not to be confused with the SQL GROUPING function, which we will look at primarily in Chapter 13. 'Grouping' here refers purely to an algebraic grouping of data which will make our above SQL statement run as we would like. Algebraic grouping in SQL allows us to use parentheses to tell SQL how to evaluate a series of operators. We could rewrite the WHERE clause to read:

WHERE
 (Manufacturer = 'BobCo'
 OR Manufacturer = 'JamesInc')
 AND (Weight < 10)

Thus, the weight condition is applied to parts made by both BobCo and JamesInc.

Try It Out - Using Multiple Filters

We would like to find all of the customers in either Washington or Oregon.

5. We have seen the query to find all of the Washington customers:

SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = 'WA'

SELECT CustomerID, CompanyName
FROM Customers
WHERE Region = 'WA'
OR Region = 'OR'

When applying multiple WHERE clauses, SQL must evaluate each row based on the criteria. In our example, we were looking for rows of data that had a region of 'WA' or a region of 'OR'. All other fields will fail the search criteria and will not be displayed.

We do not have to use the same fields for the filter. Our filter can contain multiple criteria, such as customers that spent more than $10,000 on a single line item or had a discount of more than 20%:

 SELECT OrderID
    UnitPrice * Quantity as LineItemPrice,
    Discount
FROM [Order Details]
WHERE UnitPrice * Quantity >= $10000
    OR Discount > 0.20

It is important to note here, that if you're working with MySQL, aliases must be declared at the first point at which the relevant field is mentioned. Therefore, the above statement must be modified for MySQL, thus:

SELECT OrderID AS LineItemPrice, 
    UnitPrice * Quantity as Discount
FROM Order Details
WHERE UnitPrice * Quantity >= 10000
    OR Discount > 0.20

Our queries will get much more complicated as we add groupings to our WHERE clause, or multiple operators such as AND and OR in the same criteria.

Remember that we cannot use <field> = NULL because the NULL operator requires the use of the IS keyword. Using <field> = NULL will result in no records being returned, since nothing equals NULL, not even another NULL.

Following are some errors you may encounter when executing statements similar to those above: