Including or Excluding Rows

To restrict the number of rows a Select 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, Insert Into, Insert From, 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'

For details about combining search conditions, see the following topics:

• Combining Search Conditions
  
• Specifying Multiple Search Conditions for One Column
  
  
• Specifying Multiple Search Conditions for Multiple Columns
  
  
• Combining Conditions when AND Has Precedence
  
  
• Combining Conditions when OR Has Precedence

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. 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, see the following topics.