Edit

Share via

Specify multiple search conditions for one column (Visual Database Tools)

Applies to: SQL Server

In some instances, you might want to apply a number of search conditions to the same data column. For example, you might want to:

  • Search for several different names in an employee table or for employees who are in different salary ranges. This type of search requires an OR condition.

  • Search for a book title that both starts with the word "The" and contains the word "Cook." This type of search requires an AND condition.

Note

The information in this article applies to search conditions in both the WHERE and HAVING clauses of a query. The examples focus on creating WHERE clauses, but the principles apply to both types of search conditions.

To search for alternative values in the same data column, you specify an OR condition. To search for values that meet several conditions, you specify an AND condition.

Specify an OR condition

Using an OR condition enables you to specify several alternative values to search for in a column. This option expands the scope of the search and can return more rows than searching for a single value.

Tip

You can often use the IN operator instead to search for multiple values in the same data column.

  1. In the Criteria Pane (Visual Database Tools), add the column to search.

  2. In the Filter column for the data column you just added, specify the first condition.

  3. In the Or... column for the same data column, specify the second condition.

The Query and View Designer creates a WHERE clause that contains an OR condition such as the following:

SELECT fname,
       lname
FROM employees
WHERE (salary < 30000)
      OR (salary > 100000);

Specify an AND condition

Using an AND condition enables you to specify that values in a column must meet two (or more) conditions for the row to be included in the result set. This option narrows the scope of the search and usually returns fewer rows than searching for a single value.

Tip

If you're searching for a range of values, you can use the BETWEEN operator instead of linking two conditions with AND.

  1. In the Criteria pane, add the column to search.

  2. In the Filter column for the data column you just added, specify the first condition.

  3. Add the same data column to the Criteria pane again, placing it in an empty row of the grid.

  4. In the Filter column for the second instance of the data column, specify the second condition.

The Query Designer creates a WHERE clause that contains an AND condition such as the following:

SELECT title_id,
       title
FROM titles
WHERE (title LIKE '%Cook%')
      AND (title LIKE '%Recipe%');

Related content