How to Use Logical Connectives with SQL
Often applying one condition in a query isn’t enough to return the rows you want from a SQL table. In some cases, the rows must satisfy two or more conditions. In other cases, if a row satisfies any of two or more conditions, it qualifies for retrieval.
On still other occasions, you want to retrieve only rows that don’t satisfy a specified condition. To meet these needs, SQL offers the logical connectives AND, OR, and NOT.
If multiple conditions must all be True before you can retrieve a row, use the AND logical connective. Consider the following example:
SELECT InvoiceNo, SaleDate, Salesperson, TotalSale FROM SALES WHERE SaleDate>= '2012-12-14' AND SaleDate<= '2012-12-20' ;
The WHERE clause must meet the following two conditions:
SaleDate must be greater than or equal to December 14, 2012.
SaleDate must be less than or equal to December 20, 2012.
Only rows that record sales occurring during the week of December 14 meet both conditions. The query returns only these rows.
Notice that the AND connective is strictly logical. This restriction can sometimes be confusing because people commonly use the word and with a looser meaning. Suppose, for example, that your boss says to you, “I’d like to retrieve the sales data for Ferguson and Ford.” He said, “Ferguson and Ford,” so you may write the following SQL query:
SELECT * FROM SALES WHERE Salesperson = 'Ferguson' AND Salesperson = 'Ford';
Well, don’t take that answer back to your boss. The following query is more like what the big kahuna had in mind:
SELECT * FROM SALES WHERE Salesperson IN ('Ferguson', 'Ford') ;
The first query won’t return anything, because none of the sales in the SALES table were made by both Ferguson and Ford. The second query will return the information on all sales made by either Ferguson or Ford, which is probably what the boss wanted.
If any one of two or more conditions must be True to qualify a row for retrieval, use the OR logical connective, as in the following example:
SELECT InvoiceNo, SaleDate, Salesperson, TotalSale FROM SALES WHERE Salesperson = 'Ford' OR TotalSale >200 ;
This query retrieves all of Ford’s sales, regardless of how large, as well as all sales of more than $200, regardless of who made the sales.
The NOT connective negates a condition. If the condition normally returns a True value, adding NOT causes the same condition to return a False value. If a condition normally returns a False value, adding NOT causes the condition to return a True value. Consider the following example:
SELECT InvoiceNo, SaleDate, Salesperson, TotalSale FROM SALES WHERE NOT (Salesperson = 'Ford') ;
This query returns rows for all sales transactions completed by salespeople other than Ford.
When you use AND, OR, or NOT, sometimes the scope of the connective isn’t clear. To be safe, use parentheses to make sure that SQL applies the connective to the predicate you want. In the preceding example, the NOT connective applies to the entire predicate (Salesperson = ‘Ford’).