SQL 101 4: The WHERE Clause

This is the fourth in my series of posts designed to teach people the basics of SQL and it’s use in interrogating databases.

The WHERE Clause
The WHERE clause is used to specify the conditions that must be met for the record to be selected. In the query, each record in the Table named in the FROM clause is read and tested for the condition specified in the WHERE clause. If the condition evaluates to True the Columns specified in the SELECT clause are included in the Results Table. Otherwise the record is not returned to the Results Table.

SELECT CUS_PRIMARYKEY,CUS_CODE
FROM GUSER.F_CUSTOMER
WHERE CUS_CODE LIKE '%'

This returns a Results Table containing all your Customers. (Of course the same could have been achieved in this case by eliminating the WHERE clause entirely.)

In its simplest form the WHERE clause consists of four parts: The word WHERE, a Columns name, an operator, and a value or another Column name. Several examples of a full SQL statement containing a simple WHERE clause are shown below:

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_CUCODE
FROM GUSER.F_SOHDR
WHERE SOH_DATE BETWEEN ‘2004 MAR 01’ AND ‘2004 MAR 31’

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_CUCODE
FROM GUSER.F_SOHDR
WHERE SOH_NET > 1000.00

SELECT CUS_CODE, CUS_NAME, CUS_BALANCE
FROM GUSER.F_CUSTOMER
WHERE CUS_NAME LIKE ‘Bathroom%’

SELECT CUS_CODE, CUS_NAME, CUS_BALANCE
FROM GUSER.F_CUSTOMER
WHERE CUS_NAME NOT LIKE ‘Bathroom%’

SELECT SOH_NUMBER, SOH_DATE, SOH_NET, SOH_OWNER
FROM GUSER.F_SOHDR
WHERE (SOH_OWNER = ‘JOHN CLARK’ OR SOH_OWNER = ‘PETER OAKDEN’) AND SOH_NET > 3000

Parentheses are used to alter the sequence in which conditions are evaluated. Normally conditions are evaluated multiplication/division, left to right, however parentheses cause the condition inside the parentheses to be evaluated first and to pass the results of that evaluation to the outer part of the condition (or the next outer set of parentheses if using nested parentheses)

An example of the use of parentheses, consider the following two expressions:

7 + 5 * 4        =        27
(7 + 5) * 4        =        48

The final part of the WHERE clause is the element that follows the operator. This can either be a data value, or the name of another column. If the data is alphanumeric, the data must be enclosed in quotation marks. Numeric data must not be enclosed in quotation marks. Dates are treated as alphanumeric fields and the form ‘01 JAN 06’ is commonly used.

Note: The ‘%’ symbol is a wildcard. As the query executes this is evaluated to mean ‘one or more characters’. Effectively allowing a simple ‘contains’ search. Another wildcard than can be used is the underscore character (_) , this represents one character only.

If using the wildcards then you cant use the equality operator (=), instead you must use the LIKE statement. See the examples earlier.

Tomorrow: The ORDER BY and GROUP BY Clauses.

"Good communication is as stimulating as black coffee and just as hard to sleep after." - Anne Morrow Lindbergh