About Including or Excluding Rows

Using search conditions

To restrict the number of rows a query should return, you create search conditions or filter criteria. In SQL, search conditions appear in the WHERE clause of the statement, or if you are creating an aggregate query, in the HAVING clause.

Note You can also use search conditions to indicate which rows are affected by an Update, Update Values, Delete, or Make-Table query.

When the query runs, the database engine examines and applies the search condition to each row in the tables you are searching. If the row meets the condition, it is included in the query. For example, a search condition that would find all the employees in a particular region might be:

region = 'UK'

To establish the criteria for including a row in a result, you can use multiple search conditions. For example, the following search criterion consists of two search conditions. The query includes a row in the result set only if that row satisfies both of the conditions.

region = 'UK' AND product_line = 'Housewares'

You can combine these conditions with AND or OR. The previous example uses AND. In contrast, the following criterion uses OR. The attendant query result will include any row that satisfies either or both of the search conditions:

region = 'UK' OR product_line = 'Housewares'

You can even combine search conditions on a single column. For example, the following criterion combines two conditions on the region column:

region = 'UK' OR region = 'US'

Predicates in search conditions

A search condition consists of one or more predicates, each specifying a single condition. If the search condition includes more than one predicate, the predicates are linked with a logical AND (to narrow the search) or OR (to broaden it). The following example shows how you can use multiple conditions when searching an employee table to find the employee (or employees) with the specified first and last names:

WHERE lname = 'Smith' AND fname = 'Jean'

A single predicate follows this format:

search_expression operator search_value

In most instances, search_expression is the name of a column to search. Similarly, the most common form of search_value is a literal value to search for, which can be either a string of characters or a number.

The following two examples show literal values. The first searches for all the employees who are in the United Kingdom, and the second searches for all employees with a specific job level:

WHERE region = 'UK'

WHERE job_lvl = 100

Both search_expression and search_value can consist of any (or any combination) of the following:

Literal A single text, numeric, date, or logical value. The following example uses a literal to find all rows for employees in the United Kingdom:
```
WHERE region = 'UK'
```
Column reference The name of a column in one of the tables being searched. The following example searches a products table for all rows in which the value of the production cost is lower than the shipping cost:
```
WHERE prod_cost < ship_cost
```
Function A reference to a function that the database back end can resolve to calculate a value for the search. The function can be a function defined by the database server or a user-defined function that returns a scalar value. The following example searches for orders placed today (the GETDATE( ) function returns the current date):
```
WHERE order_date = GETDATE()
```
NULL The following example searches an authors table for all authors who have a first name on file:
```
WHERE au_fname IS NOT NULL
```
Calculation The result of a calculation that can involve literals, column references, or other expressions. The following example searches a products table to find all rows in which the retail sales price is more than twice the production cost:
```
WHERE sales_price > (prod_cost * 2)
```
Subquery A result set generated by another query. The following example searches a products table to find all the products from Swedish suppliers. The subquery first searches the suppliers table to build a list of the suppliers located in that country/region. The second search then searches the products table, matching the productÆs supplier ID against the list created by the subquery:
```
WHERE supplier_id IN
 (SELECT supplier.supplier_id
 FROM supplier
 WHERE (supplier.country = 'Sweden'))
```