WHERE Clause

Learn how to filter query results in SQL Server using the WHERE clause, comparison operators, logical operators, BETWEEN, IN, and LIKE.

Introduction

Retrieving every row from a table is rarely useful in a real application — you usually want a subset that matches specific criteria. The WHERE clause is how SQL Server filters rows, letting you ask questions like "which employees earn more than 60000" or "which orders were placed last month." This lesson covers the operators you will use constantly when filtering data.

What You Will Learn

How to filter rows using comparison operators.
How to combine conditions with AND, OR, and NOT.
How to filter ranges of values with BETWEEN.
How to match a list of values with IN.
How to perform pattern matching with LIKE.

Comparison Operators

T-SQL supports the standard set of comparison operators: = for equality, <> or != for inequality, and >, <, >=, <= for greater than, less than, and their inclusive variants. These are placed after WHERE, followed by the condition to test.

Comparison Operators

SELECT FirstName, Department, Salary
FROM Employees
WHERE Salary > 55000;

Result

Click Run to see what this code prints.

AND, OR, and NOT

AND requires all conditions to be true. OR requires at least one condition to be true. NOT negates a condition. When mixing AND and OR, use parentheses to make the intended evaluation order explicit — SQL Server evaluates AND before OR by default, which can produce surprising results if you rely on it implicitly.

Combining Conditions

SELECT FirstName, Department, Salary
FROM Employees
WHERE Department = 'Engineering' AND Salary > 60000;

SELECT FirstName, Department
FROM Employees
WHERE Department = 'Sales' OR Department = 'Marketing';

SELECT FirstName, Department
FROM Employees
WHERE NOT Department = 'Engineering';

Result (first query)

Click Run to see what this code prints.

BETWEEN

BETWEEN checks whether a value falls within an inclusive range. It is equivalent to writing value >= low AND value <= high, but it reads more naturally. BETWEEN works with numbers, dates, and even strings.

BETWEEN

SELECT FirstName, Salary
FROM Employees
WHERE Salary BETWEEN 55000 AND 70000;

Result

Click Run to see what this code prints.

IN

IN lets you check whether a value matches any value in a list, replacing a long chain of OR conditions with a compact, readable syntax.

IN

SELECT FirstName, Department
FROM Employees
WHERE Department IN ('Sales', 'Marketing', 'Finance');

Result

Click Run to see what this code prints.

LIKE

LIKE performs pattern matching on string columns using wildcards: % matches any sequence of zero or more characters, and _ matches exactly one character. It is commonly used for searching names or partial text matches.

LIKE

-- Names starting with 'A'
SELECT FirstName FROM Employees WHERE FirstName LIKE 'A%';

-- Names containing 'an'
SELECT FirstName FROM Employees WHERE FirstName LIKE '%an%';

Result (first query)

Click Run to see what this code prints.

NULL Comparisons

You cannot use = NULL or <> NULL to test for NULL values — comparisons with NULL always evaluate to UNKNOWN. Use IS NULL or IS NOT NULL instead.

Common Mistakes

Avoid These Mistakes

Using = NULL instead of IS NULL to check for missing values.
Mixing AND and OR without parentheses, changing the intended logic.
Forgetting that string comparisons in SQL Server are case-insensitive by default under most collations, which can surprise developers used to case-sensitive systems.
Using LIKE with a leading wildcard (e.g. '%an') on large tables, which prevents efficient index usage.
Writing WHERE Salary BETWEEN 70000 AND 55000 with the bounds reversed, which returns no rows.

Best Practices

Use parentheses to make AND/OR precedence explicit, even when not strictly required.
Prefer IN over long chains of OR for readability.
Use IS NULL and IS NOT NULL for NULL checks, never = or <>.
Be cautious with leading-wildcard LIKE patterns on large, indexed tables.
Filter as early and precisely as possible to reduce the number of rows SQL Server has to process.

Frequently Asked Questions

Is WHERE case-sensitive in SQL Server?

It depends on the database collation. Most default SQL Server collations are case-insensitive, so WHERE Name = 'amit' will match 'Amit'. A case-sensitive collation would behave differently.

Can I use WHERE with UPDATE and DELETE, not just SELECT?

Yes. WHERE works identically across SELECT, UPDATE, and DELETE statements to determine which rows are affected.

What is the difference between BETWEEN and using >= and <= directly?

They are functionally equivalent — BETWEEN is simply more concise. Both are inclusive of the boundary values.

Does NOT IN handle NULL values safely?

Not always. If the list used with NOT IN contains a NULL, the entire condition can unexpectedly return no rows. Use NOT EXISTS or filter out NULLs explicitly when in doubt.

Key Takeaways

WHERE filters rows based on a condition.
Comparison operators include =, <>, >, <, >=, and <=.
AND, OR, and NOT combine multiple conditions.
BETWEEN checks inclusive ranges; IN checks membership in a list.
LIKE performs pattern matching using % and _ wildcards.

Summary

The WHERE clause is essential for narrowing query results down to exactly the rows you care about. Next, you will learn how to control the order in which those rows are returned using ORDER BY.

Next Lesson →

ORDER BY