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'
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:
WHERE region = 'UK'
products
table for all rows in which the value of the production cost is lower than the shipping cost:WHERE prod_cost < ship_cost
WHERE order_date = GETDATE()
authors
table for all authors who have a first name on file:WHERE au_fname IS NOT NULL
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)
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.
For information about | See |
Specifying search conditions in the Query Designer | Specifying Search Conditions |
How to create expressions that you can use in search conditions | Using Expressions in Queries |
The operators you can use in search conditions | Comparison Operators, Logical Operators, and Wildcard Characters |
How to enter 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 |