The WHERE Clause

WHERE Clause"-->

only for RuBoard

The WHERE Clause

Filters allow you to limit the number of rows returned by your queries. You set criteria to filter out rows that don't match a certain conditional statement. The SQL WHERE clause is always going to be in front of any conditions you specify.

You can filter which rows will be returned when running SQL queries by using the SQL WHERE clause. This clause enables you specify a search condition in the query to restrict the number of rows returned in the result set. Listing 4.3 illustrates the basic structure of the SQL WHERE clause.

Listing 4.3 The Basic Structure of the WHERE Clause

 [ WHERE < search_condition > 

Using the preceding SELECT statement example, you can filter out all products except those supplied by the supplier with the SupplierID of 2:

 1: SELECT * 2: FROM Products 3: WHERE SupplierID = 2 

When the preceding query is executed, rows in the Products table will be returned only if they have a SupplierID that equals 2. You can limit the returned data even further by using an additional condition statement. If you do use more than one condition statement you separate the criteria by using the AND operator, as shown in the following example:

 1: SELECT * 2: FROM Products 3: WHERE SupplierID = 2 AND UnitPrice = 22 

In the preceding situation, the only rows that will be returned are those that contain a SupplierID equal to 2 and a UnitPrice equal to 22. When this query is executed, only one row is returned in the result set.

I would suggest researching all the different search condition arguments available to you using SQL (see Table 4.1). Using search conditions can significantly reduce your application's overhead. I have seen some applications that return twice as many rows as needed for their function. This is all fine and good for a single- user application, but it could be devastating to the performance of a Web site that gets 1,000+ concurrent users and could potentially crash your server.

Table 4.1. Some Useful Arguments for Search Criteria

Argument	Condition It Tests
=	Equality between two expressions
<>	Two expressions not equal to each other
!=	Two expressions not equal to each other
>	One expression greater than the other
>=	One expression greater than or equal to the other
!>	One expression not greater than the other
<	One expression less than the other
<=	One expression less than or equal to the other
!<	One expression not less than the other

only for RuBoard