Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 39 ยท T-SQL Querying

Filtering Rows with WHERE in T-SQL

Filter rows in SQL Server with WHERE: comparison operators, AND/OR/NOT, IN, BETWEEN, LIKE wildcards, IS NULL, three-valued logic and writing index-friendly conditions.

WHERE decides which rows reach the rest of the query. Good filters return exactly what you need; careless ones return too much, too little, or force SQL Server to read an entire table. This lesson covers every operator you need, the surprising three-valued logic that makes NULL special, T-SQL's wildcard patterns, and the idea of sargable conditions that let indexes do their job.

All the examples run on the shop database unless they use T-SQL-only syntax.

Comparison and logical operators

Operator Meaning
=, <> (or !=) equal, not equal
<, <=, >, >= ordering comparisons
AND, OR, NOT combine conditions

AND binds tighter than OR, so a OR b AND c means a OR (b AND c). Use parentheses whenever you mix them; it costs nothing and prevents bugs.

SQL Combining conditions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

IN, BETWEEN and NOT

IN tests membership in a list; it reads better than a chain of ORs:

SQL
SELECT name FROM dbo.customers WHERE country IN (N'France', N'Spain', N'Italy');

BETWEEN a AND b is inclusive at both ends. That is perfect for numbers but dangerous for date-times, as the next section shows.

SQL IN and BETWEEN ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

NOT IN has a trap: if the list or subquery contains a NULL, the condition can never be true and the query returns nothing. Use NOT EXISTS for subqueries instead (set operators and subqueries).

Filtering dates correctly

Suppose ordered_at is a DATETIME2. This looks right and is wrong:

SQL
WHERE ordered_at BETWEEN '2025-03-01' AND '2025-03-31'   -- misses March 31 after midnight

Use a half-open range: start included, end excluded.

SQL
WHERE ordered_at >= '20250301' AND ordered_at < '20250401'

In T-SQL, the unseparated yyyymmdd literal is the safest string format because it is independent of language and SET DATEFORMAT settings.

Pattern matching with LIKE

LIKE compares text to a pattern. T-SQL supports these wildcards:

Wildcard Meaning
% any number of characters (including none)
_ exactly one character
[abc] one character from the set
[a-f] one character in the range
[^abc] one character not in the set
SQL
SELECT * FROM dbo.customers WHERE last_name LIKE N'Mc%';        -- starts with Mc
SELECT * FROM dbo.customers WHERE email LIKE N'%@example.com';  -- ends with
SELECT * FROM dbo.products  WHERE name LIKE N'[A-C]%';          -- starts with A, B or C
SELECT * FROM dbo.products  WHERE name LIKE N'%50\%%' ESCAPE N'\'; -- literal percent sign

Case sensitivity depends on the collation. The default SQL_Latin1_General_CP1_CI_AS is case-insensitive (CI), so LIKE 'mc%' matches McBride. The portable wildcards % and _ work everywhere, so you can try them live; the bracket sets are T-SQL-specific.

SQL LIKE patterns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A pattern that starts with % cannot use an index seek, because there is no known starting point; SQL Server must scan. For heavy text search, look at full-text indexes.

NULL and three-valued logic

NULL means "unknown", not "empty" or zero. Any comparison with NULL yields unknown, and WHERE keeps only rows where the condition is true:

SQL
WHERE phone = NULL      -- never true, even for rows where phone is NULL
WHERE phone IS NULL     -- correct
WHERE phone IS NOT NULL

Unknown propagates: NULL = NULL is unknown, NULL <> 5 is unknown, NULL OR TRUE is true, NULL AND TRUE is unknown. Aggregates such as AVG ignore NULLs, and COUNT(column) counts only non-null values while COUNT(*) counts rows.

SQL IS NULL versus = NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Writing sargable conditions

A condition is sargable (Search ARGument ABLE) when SQL Server can use an index seek to find matching rows directly. Wrapping the column in a function usually makes it non-sargable, because the index stores the raw column values, not the function's output:

Slow (non-sargable) Better (sargable)
WHERE YEAR(ordered_at) = 2025 WHERE ordered_at >= '20250101' AND ordered_at < '20260101'
WHERE LEFT(last_name, 2) = N'Mc' WHERE last_name LIKE N'Mc%'
WHERE price * 1.2 > 100 WHERE price > 100 / 1.2
WHERE ISNULL(status, N'') = N'new' WHERE status = N'new'

Move the calculation to the other side of the comparison so the column stands alone. Chapter 7 returns to this with execution plans.

Habit: when a query is slow, first look at the WHERE clause for functions around columns, mismatched data types and leading wildcards.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does WHERE price = NULL return?

    A comparison with NULL is unknown, never true. Use IS NULL.
  2. 2. Which pattern matches names starting with A, B or C?

    [A-C] is a T-SQL character range wildcard that matches one character from A to C.
  3. 3. Why is WHERE YEAR(ordered_at) = 2025 usually slow on a big table?

    The index is ordered by raw ordered_at values; applying a function to each row forces a scan. Use a date range instead.
  4. 4. Which range is the safest way to select all of March 2025 from a DATETIME2 column?

    A half-open range includes every moment of March 31 and does not depend on the time precision.

Summary

  • Use parentheses when mixing AND and OR; IN and BETWEEN (inclusive) simplify filters.
  • Filter dates with half-open ranges, not BETWEEN on the last day.
  • LIKE supports %, _, [ ] and [^ ]; collation decides case sensitivity.
  • Compare with NULL using IS NULL and IS NOT NULL.
  • Keep columns bare on one side of the comparison so the condition stays sargable.

Next: Sorting and Paging with ORDER BY, OFFSET and FETCH