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.
On this page
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:
SELECT
CustomerId,
FirstName,
LastName,
City
FROM dbo.Customers
WHERE State = 'FL';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.
SELECT
CustomerId,
FirstName,
LastName,
CreatedDate
FROM dbo.Customers
WHERE CreatedDate >= '20250501';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, notCustomerId > '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 fordatecolumns likeCreatedDate. 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 asState = '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:
SELECT
CustomerId,
FirstName,
LastName,
City
FROM dbo.Customers
WHERE State = 'FL'
AND City = 'Miami';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:
SELECT
CustomerId,
FirstName,
LastName,
State
FROM dbo.Customers
WHERE State = 'FL'
OR State = 'TX';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:
SELECT
CustomerId,
FirstName,
LastName,
City,
State
FROM dbo.Customers
WHERE State = 'FL'
OR State = 'TX'
AND City = 'Dallas';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:
SELECT
CustomerId,
FirstName,
LastName,
City,
State
FROM dbo.Customers
WHERE (State = 'FL' OR State = 'TX')
AND City = 'Dallas';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:
SELECT
CustomerId,
FirstName,
LastName,
State
FROM dbo.Customers
WHERE State IN ('FL', 'TX', 'WA');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:
SELECT
CustomerId,
FirstName,
LastName,
CreatedDate
FROM dbo.Customers
WHERE CreatedDate BETWEEN '20250101' AND '20250331';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:
SELECT
CustomerId,
FirstName,
LastName,
CreatedDate
FROM dbo.Customers
WHERE CreatedDate >= '20250101'
AND CreatedDate < '20250401';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.
SELECT
CustomerId,
FirstName,
LastName
FROM dbo.Customers
WHERE LastName LIKE 'W%';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:
SELECT
CustomerId,
FirstName,
LastName,
State
FROM dbo.Customers
WHERE State NOT IN ('FL', 'TX');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:
SELECT
CustomerId,
FirstName,
LastName,
Email
FROM dbo.Customers
WHERE Email IS NULL;One row: Isla Brooks. The opposite is IS NOT NULL, which returns the other nine customers:
SELECT
CustomerId,
FirstName,
LastName,
Email
FROM dbo.Customers
WHERE Email IS NOT NULL;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.
- Find the customers in Florida.
- Find the customers in Florida or Texas, using
IN. - Find the customers who have no email address.
- Find the customers whose last name begins with W.
- Find the customers created after 1 May 2025.
- Find the Florida customers created in the first quarter of 2025, using a half-open date range.
Practice answers
-- 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';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
SQL Server Foundations
Lesson 6 of 25
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetSQL Server Wait TypesUse SQL Server wait statistics to identify where work is spending time, distinguish current waits from cumulative waits, and choose the next performance investigation.Open cheat sheet
- ArticleAggregate Functions in SQL Server: COUNT, SUM, AVG, MIN, and MAXLearn how SQL Server aggregate functions summarize customer and order rows with COUNT, SUM, AVG, MIN, and MAX.Read article
- RoadmapSQL Server FoundationsFilter 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.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.