Specifying Search Criteria

To restrict the number of rows a Select query should return, or to specify the rows affected by an Update, Insert Values, Insert, Delete, or Make Table query query, 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.

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'

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 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. 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'))
  

For more details about creating search conditions, refer to the topics listed in the following table.