Home > Data Access / ADO.NET > Structured Query Language (SQL) Basics > SQL Where: Specifying a Filter Condition

---

The WHERE clause specifies the criteria expression, or a filter condition that records must meet to be included in the query results.

When included, WHERE follows FROM. If a WHERE clause is not specified, the query returns all rows from the table.

SELECT * FROM tableName WHERE filterCondition

in which filterCondition takes the form

columnName operator expression

An operator is a symbol specifying an action that is performed on one or more expressions.

The SQL Server Database Engine uses an extensive set of operators, but here we shall focus only on two basic operator categories: comparison and logical operators.

Comparison operators are used to test whether two expressions are the same.

The following table lists the more common comparison operators that you should be familiar with.

NOTE: NOT ALL data source providers support ALL the above operators. Always test to ensure whether a specific provider supports the comparator.

Logical operators are used to test if a given condition is true.

The following table lists the more common logical operators that you should be familiar with.

You can use any valid expression to determine which records the SQL statement returns. The filter condition can take any of the forms in the following examples.

String values are delimited with single quotes.

query = "SELECT . . . WHERE State = 'CA'"

You can include as many filter conditions as needed in a WHERE clause, connecting them with the AND or OR operator.

query = "SELECT . . . WHERE Country = 'US' AND State = 'CA'"

Using the WHERE Clause (Example 1) Run Sample | View Source

You can also use the NOT operator to reverse the value of a logical expression.

query = "SELECT . . . WHERE Country = 'US' AND NOT State = 'CA'"

Numeric values are stated as numbers that can contain decimals.

query = "SELECT . . . WHERE Area >= 6000"

Using the WHERE Clause (Example 2) Run Sample | View Source

Date literals can be any sequence of characters with a valid format, delimited with single quotes.

query = "SELECT . . . WHERE OrderDate <= '12/31/24' ... "

Using the WHERE Clause (Example 3) Run Sample | View Source

Use date literals to maximize portability across national languages.

The following shows using BETWEEN to specify the lower and upper bounds of the data range being filtered. For example, to select all items in the Orders table shipped between January 1, 2023 and December 31, 2023.

query = "SELECT . . . WHERE ShippedDate BETWEEN '1/1/23' AND '12/31/23' . . . "

Using the WHERE Clause (Example 4) Run Sample | View Source

When the filter condition includes IN, the column must contain one of the values in a given set of values, for the record to be included in the query results.

For example, to select only the records related to specific product types, you can set a filter condition in which the ProductType can be found IN a comma-separated list of values:

query = "SELECT . . . WHERE ProductType IN ( 'Shirt', 'RockShoes', 'Supplies' )"

Using the WHERE ... IN Clause (Example 1) Run Sample | View Source

You can also use the NOT operator to reverse the outcome of the expression. Here, the column must NOT contain any of the values found IN the set before its record is included in the query results.

query = "SELECT . . . WHERE NOT StateId IN ( 'AA', 'AE', 'AP' ) ... "

Using the WHERE IN Clause (Example 2) Run Sample | View Source

The following demonstrates using the IN keyword to compare against values returned by a subquery, or a SELECT statement within another SELECT statement.

query = "SELECT . . . WHERE CountryCode IN ( SELECT DISTINCT CountryCode FROM States )"

Using the WHERE IN Clause (Example 3) Run Sample | View Source

IMPORTANT: Only one expression can be specified in the subquery. It must return values only from a single column, otherwise an error will occur.

SQL JOIN ON: Selecting Data From More Than One Table