Filtering Data with WHERE in SQL Server

The WHERE clause filters rows returned by a query so you only get the data you need. Without it, every row in the table is returned. SQL Server supports a rich set of operators for building precise filter conditions.

Comparison Operators

The basic comparison operators are =, != (or <>), <, >, <=, and >=. These work with numbers, strings, and dates.

-- Exact match
SELECT * FROM Employees
WHERE Department = 'Engineering';

-- Numeric comparison
SELECT * FROM Products
WHERE Price >= 50.00;

-- Date comparison
SELECT * FROM Orders
WHERE OrderDate > '2024-01-01';

AND, OR, and NOT

Combine multiple conditions with logical operators. Use parentheses to control evaluation order and avoid unexpected results.

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Department = 'Sales'
  AND Salary > 60000
  AND NOT IsTerminated = 1;

SELECT * FROM Products
WHERE (Category = 'Electronics' OR Category = 'Accessories')
  AND Stock > 0;

LIKE for Pattern Matching

The LIKE operator matches string patterns. Use % for any sequence of characters and _ for a single character.

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

-- Email from a specific domain
SELECT * FROM Users
WHERE Email LIKE '%@unifysql.com';

-- Exactly 5 characters
SELECT * FROM Products
WHERE SKU LIKE '_____';

IN and BETWEEN

IN checks if a value matches any item in a list. BETWEEN checks if a value falls within an inclusive range. Both simplify conditions that would otherwise require multiple OR statements.

-- IN with a list of values
SELECT * FROM Employees
WHERE Department IN ('Engineering', 'Design', 'Product');

-- BETWEEN for ranges (inclusive)
SELECT * FROM Orders
WHERE OrderTotal BETWEEN 100 AND 500;

-- BETWEEN with dates
SELECT * FROM Events
WHERE EventDate BETWEEN '2024-06-01' AND '2024-06-30';

Handling NULLs

NULL represents missing or unknown data. You cannot compare NULL with = or !=. Instead, use IS NULL and IS NOT NULL.

-- Find employees without a manager
SELECT * FROM Employees
WHERE ManagerID IS NULL;

-- Find completed orders
SELECT * FROM Orders
WHERE ShippedDate IS NOT NULL;

Using the WHERE clause effectively is essential for writing performant queries. Always filter early to reduce the amount of data SQL Server needs to process, and make sure your filter columns are indexed for best performance.