SQL Server

Filtering Data with WHERE in SQL Server

Filter rows with the WHERE clause in SQL Server: comparison operators, AND and OR, IN, BETWEEN, LIKE, NOT, IS NULL, AND/OR precedence, and the data-type details that trip up beginners, with exercises.

By JaviPublished 13 min readBeginner

Most questions you ask a database are not about all the rows. They are about the customers in one state, the orders from last month, the accounts without an email address. The clause that narrows a table down to the rows you care about is WHERE.

You have already seen it once, in your first SELECT query, and in the previous lesson you learned that it is logically evaluated right after FROM, before grouping and before SELECT. This lesson, the sixth step of SQL Server Foundations, covers everything a beginner needs to filter rows confidently. All examples run against dbo.Customers in the JaviBuildsLab practice database, and the results described are the ones you will see.

How WHERE decides

WHERE is followed by a condition. SQL Server tests the condition against each row from FROM, and keeps a row only when the condition is true. Rows where it is false are dropped, and so are rows where the answer is unknown, which happens when a NULL is involved. Keep that last detail in mind; it comes back at the end.

Equality and comparison operators

The simplest condition compares a column with a value:

SQL query: Equality and comparison operatorsT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    City
FROM dbo.Customers
WHERE State = 'FL';
T-SQL7 linesRead-only query display. It is not connected to a database.

Three rows: Ana Torres, David Kim and Farid Haddad. T-SQL has six comparison operators:

Operator Meaning Example
= equal to State = 'FL'
<> not equal to State <> 'FL'
> greater than CustomerId > 7
>= greater than or equal to CreatedDate >= '20250501'
< less than CustomerId < 3
<= less than or equal to CreatedDate <= '20250131'

SQL Server also accepts != for “not equal”. It works, but <> is the standard form and the one you will see most in SQL Server code.

SQL query: Equality and comparison operators (2)T-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    CreatedDate
FROM dbo.Customers
WHERE CreatedDate >= '20250501';
T-SQL7 linesRead-only query display. It is not connected to a database.

Four customers were created on or after 1 May 2025: Grace Wu, Hugo Silva, Isla Brooks and Jonas Weber.

Quotes, numbers, dates and data types

A few details decide whether a condition does what you mean.

  • Text goes in single quotes: 'FL', 'Miami'. Double quotes mean a name, not a value, as lesson 3 showed.
  • Numbers do not: CustomerId > 7, not CustomerId > '7'. With quotes, SQL Server has to convert the text to a number. It usually manages, but it is a habit that causes trouble later.
  • Dates are written as text in an unambiguous format. '20250501' (year, month, day, no separators) is read the same way on every server. '2025-05-01' is also safe for date columns like CreatedDate. Avoid '05/01/2025': depending on the server’s language settings it is 1 May or 5 January.
  • The column’s data type matters. Compare text columns with text, number columns with numbers and date columns with dates. Check the type in Object Explorer under the table’s Columns folder if you are unsure.
  • Case sensitivity depends on the collation. Most SQL Server databases use a case-insensitive collation, so State = 'fl' finds the same rows as State = 'FL'. Some databases are case-sensitive, and there it would find nothing. Writing values in the same case as the data is the safe habit.

Combine conditions with AND

AND keeps a row only if both conditions are true:

SQL query: Combine conditions with ANDT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    City
FROM dbo.Customers
WHERE State = 'FL'
  AND City = 'Miami';
T-SQL8 linesRead-only query display. It is not connected to a database.

One row: Ana Torres. Each AND makes the filter stricter. Putting each condition on its own line, with AND at the start, makes long filters easy to read and easy to comment out while testing.

Combine conditions with OR

OR keeps a row if at least one condition is true:

SQL query: Combine conditions with ORT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    State
FROM dbo.Customers
WHERE State = 'FL'
   OR State = 'TX';
T-SQL8 linesRead-only query display. It is not connected to a database.

Five rows: the three Florida customers plus Ben Carter and Hugo Silva in Texas. Each OR makes the filter looser.

AND and OR together: use parentheses

This is the classic beginner trap. You want customers in Florida or Texas who live in Dallas, and you write:

SQL query: AND and OR together: use parenthesesT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    City,
    State
FROM dbo.Customers
WHERE State = 'FL'
   OR State = 'TX'
  AND City = 'Dallas';
T-SQL10 linesRead-only query display. It is not connected to a database.

You get four rows: all three Florida customers and Hugo Silva. The reason is that AND is evaluated before OR, just as multiplication comes before addition. SQL Server reads the condition as State = 'FL' OR (State = 'TX' AND City = 'Dallas'): every Florida customer, plus Texans in Dallas.

Parentheses make your intent explicit:

SQL query: AND and OR together: use parentheses (2)T-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    City,
    State
FROM dbo.Customers
WHERE (State = 'FL' OR State = 'TX')
  AND City = 'Dallas';
T-SQL9 linesRead-only query display. It is not connected to a database.

Now one row: Hugo Silva. The rule to remember is simple: whenever a WHERE mixes AND and OR, add parentheses, even when you think the default order happens to be right. The next person to read the query will not have to work it out.

IN: one of several values

When you compare one column with a list of values, IN is shorter than a chain of OR:

SQL query: IN: one of several valuesT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    State
FROM dbo.Customers
WHERE State IN ('FL', 'TX', 'WA');
T-SQL7 linesRead-only query display. It is not connected to a database.

Six rows: the Florida and Texas customers plus Chloe Martin in Washington. State IN ('FL', 'TX') means exactly the same as State = 'FL' OR State = 'TX', and because it is a single condition, it cannot cause the AND/OR surprise above.

BETWEEN: a range, both ends included

BETWEEN tests whether a value falls inside a range, including both ends:

SQL query: BETWEEN: a range, both ends includedT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    CreatedDate
FROM dbo.Customers
WHERE CreatedDate BETWEEN '20250101' AND '20250331';
T-SQL7 linesRead-only query display. It is not connected to a database.

Four customers were created in the first quarter of 2025: Ana Torres, Ben Carter, Chloe Martin and David Kim. CustomerId BETWEEN 3 AND 5 returns customers 3, 4 and 5.

Be careful with dates that carry a time. CreatedDate is a date column, so it holds whole days and the query above is correct. Many tables use datetime or datetime2 columns instead, which store a time as well. There, '20250331' means midnight at the start of 31 March, so BETWEEN '20250101' AND '20250331' silently misses everything that happened later that day. The pattern that is always right, whatever the column type, is a half-open range: on or after the first day, and before the day after the last:

SQL query: BETWEEN: a range, both ends included (2)T-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    CreatedDate
FROM dbo.Customers
WHERE CreatedDate >= '20250101'
  AND CreatedDate < '20250401';
T-SQL8 linesRead-only query display. It is not connected to a database.

It returns the same four customers here, and it keeps working if the column ever gains a time part.

LIKE: text patterns

LIKE compares text with a pattern. Two wildcards cover most needs:

  • % matches any number of characters, including none.
  • _ matches exactly one character.
SQL query: LIKE: text patternsT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName
FROM dbo.Customers
WHERE LastName LIKE 'W%';
T-SQL6 linesRead-only query display. It is not connected to a database.

Two rows: Grace Wu and Jonas Weber. LastName LIKE '%er' finds last names ending in er (Carter and Weber), and LastName LIKE 'K_m' finds Kim. A pattern that starts with %, such as '%er', works fine on small tables but forces SQL Server to check every row; on very large tables that becomes a performance question for later.

NOT: the opposite

NOT reverses a condition, and <> is the “not equal” comparison:

SQL query: NOT: the oppositeT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    State
FROM dbo.Customers
WHERE State NOT IN ('FL', 'TX');
T-SQL7 linesRead-only query display. It is not connected to a database.

Five rows: the customers in WA, CO, OR, MA and IL. You can also write State <> 'FL' (eight rows), LastName NOT LIKE 'W%' (eight rows) or CreatedDate NOT BETWEEN '20250101' AND '20250331' (six rows).

NULL: IS NULL and IS NOT NULL

NULL means no value. In our table, Isla Brooks has no email. To find rows where a value is missing, use IS NULL:

SQL query: NULL: IS NULL and IS NOT NULLT-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    Email
FROM dbo.Customers
WHERE Email IS NULL;
T-SQL7 linesRead-only query display. It is not connected to a database.

One row: Isla Brooks. The opposite is IS NOT NULL, which returns the other nine customers:

SQL query: NULL: IS NULL and IS NOT NULL (2)T-SQL
SELECT
    CustomerId,
    FirstName,
    LastName,
    Email
FROM dbo.Customers
WHERE Email IS NOT NULL;
T-SQL7 linesRead-only query display. It is not connected to a database.

Do not write WHERE Email = NULL. It looks reasonable and it does not raise an error, but it returns no rows at all. A comparison with NULL is never true: SQL Server cannot say whether an unknown value equals anything, so the answer is unknown, and WHERE drops rows whose answer is not true. The same logic has a side effect worth knowing early: WHERE Email <> 'ana.torres@example.com' returns eight rows, not nine, because Isla Brooks’s NULL email is not “different from” Ana’s either; it is unknown. NULL gets a full lesson later in this stage.

Check your filter before you trust it

A filter that runs without errors can still be wrong. A few habits catch most mistakes before anyone relies on the result:

  • Know the total first. SELECT COUNT(*) FROM dbo.Customers; returns 10. A filter that returns 10 rows when you expected a handful probably did not filter at all, which is exactly what happened in the AND/OR example above.
  • Count before you look. Put COUNT(*) in place of the column list to see how many rows a condition keeps. On a large table, a count is faster to read than thousands of rows in the grid.
  • Add one condition at a time. Write the first condition, run it, check the count, then add the next. If the result suddenly jumps or drops to zero, the last condition you added is the one to inspect.
  • Comment out instead of deleting. With one condition per line, -- in front of a line switches it off. That makes it quick to see which condition removes which rows.
  • Look at a few rows, not just the count. Check that the rows you kept are the rows you meant, especially around boundaries such as the first and last day of a date range.

This habit matters far beyond reading data. Later in this path, the same WHERE clause decides which rows an UPDATE changes or a DELETE removes. The safe way to write those statements is to run the matching SELECT first and confirm it returns exactly the rows you expect. Practicing that check now, while nothing can be damaged, makes it automatic when it counts.

Try it yourself

Run each of these against JaviBuildsLab. Write your own query first, then compare it with the answers in the next section.

  1. Find the customers in Florida.
  2. Find the customers in Florida or Texas, using IN.
  3. Find the customers who have no email address.
  4. Find the customers whose last name begins with W.
  5. Find the customers created after 1 May 2025.
  6. Find the Florida customers created in the first quarter of 2025, using a half-open date range.

Practice answers

SQL query: Practice answersT-SQL
-- 1. Customers in Florida (3 rows)
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE State = 'FL';

-- 2. Florida or Texas (5 rows)
SELECT CustomerId, FirstName, LastName, State
FROM dbo.Customers
WHERE State IN ('FL', 'TX');

-- 3. No email address (1 row: Isla Brooks)
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE Email IS NULL;

-- 4. Last name begins with W (2 rows: Wu, Weber)
SELECT CustomerId, FirstName, LastName
FROM dbo.Customers
WHERE LastName LIKE 'W%';

-- 5. Created after 1 May 2025 (4 rows)
SELECT CustomerId, FirstName, LastName, CreatedDate
FROM dbo.Customers
WHERE CreatedDate > '20250501';

-- 6. Florida, first quarter of 2025 (2 rows: Ana Torres, David Kim)
SELECT CustomerId, FirstName, LastName, CreatedDate
FROM dbo.Customers
WHERE State = 'FL'
  AND CreatedDate >= '20250101'
  AND CreatedDate < '20250401';
T-SQL31 linesRead-only query display. It is not connected to a database.

If your answer to question 6 used BETWEEN '20250101' AND '20250331', it returns the same rows on this date column. The half-open range is the version that stays correct on datetime columns.

Next lesson

You can now filter rows with comparisons, combine conditions safely with AND, OR and parentheses, and use IN, BETWEEN, LIKE, NOT and IS NULL. The next lesson, Sorting Data with ORDER BY in SQL Server, covers ascending and descending order, several sort columns and TOP.

Check what you learned

2 quick questions

Question 1 of 2What does WHERE do in a SELECT query?
Question 2 of 2When should a text value usually be enclosed in SQL Server?

SQL Server Foundations

Lesson 6 of 25

Continue to next lesson

Sorting Data with ORDER BY in SQL Server

Continue to Lesson 7

Tags

  • SQL Server
  • SQL Fundamentals
  • SSMS
  • T-SQL