Including or Excluding Rows

Visual Database Tools

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:

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.

For information about	See
Specifying search conditions in the Query Designer	Specifying Search Conditions
Creating expressions that you can use in search conditions	Using Expressions in Queries
Using operators in search conditions	Comparison Operators, Logical Operators, and Wildcard Characters
Entering text, numbers, dates, or logical values	Entering Search Values
Finding rows that do not match a value	Selecting Rows that Do Not Match a Value
Removing duplicate rows from Select queries	Excluding Duplicate Rows
Applying multiple search conditions to the same data column	Specifying Multiple Search Conditions for One Column
Including several data columns as part of the search condition for a query	Specifying Multiple Search Conditions for Multiple Columns
Linking search conditions with AND and OR operators	Combining Search Conditions
Using subqueries	Creating Subqueries

previous page start next page