LIKE, IN, BETWEEN: Advanced Filtering Patterns in SQL Server

Basic equality filters only get you so far. Here’s the rest of the filtering toolkit — wildcard patterns, membership tests, ranges, and combining them all with AND/OR/NOT correctly — plus a NULL-comparison bug that costs beginners hours of confused debugging because it never throws an error.

Comparison and Logical Operators

SELECT name FROM dbo.Restaurant WHERE rating >= 4.5;
SELECT name FROM dbo.Restaurant WHERE city = 'Austin' AND rating > 4.0;
SELECT name FROM dbo.Restaurant WHERE cuisine = 'Indian' OR cuisine = 'Japanese';
SELECT name FROM dbo.Restaurant WHERE NOT (city = 'Dallas');

Operator Precedence: A Real Beginner Trap

-- Looks like "Austin restaurants, plus anything highly rated" — but AND binds tighter than OR:
SELECT name FROM dbo.Restaurant
WHERE city = 'Austin' OR cuisine = 'Italian' AND rating > 4.5;
-- Actually means: city = 'Austin' OR (cuisine = 'Italian' AND rating > 4.5)

-- Use explicit parentheses to say what you actually mean:
SELECT name FROM dbo.Restaurant
WHERE (city = 'Austin' OR cuisine = 'Italian') AND rating > 4.5;

IN: A Cleaner Way to Write Multiple ORs

SELECT name FROM dbo.Restaurant
WHERE cuisine IN ('Indian', 'Japanese', 'Italian');

-- NOT IN excludes a list — but see the NULL warning below before relying on this
SELECT name FROM dbo.Restaurant
WHERE cuisine NOT IN ('Mexican');

BETWEEN: Inclusive on Both Ends

SELECT name, rating FROM dbo.Restaurant
WHERE rating BETWEEN 4.0 AND 4.5;
-- Equivalent to: rating >= 4.0 AND rating <= 4.5 — both endpoints are included

LIKE: Pattern Matching

SELECT name FROM dbo.Restaurant WHERE name LIKE 'T%';        -- starts with T
SELECT name FROM dbo.Restaurant WHERE name LIKE '%Grill%';    -- contains "Grill" anywhere
SELECT name FROM dbo.Restaurant WHERE name LIKE '_a%';        -- 2nd letter is 'a'
SELECT name FROM dbo.Restaurant WHERE name LIKE '[ST]%';      -- starts with S or T

The NULL Trap

NULL means "unknown," and SQL uses three-valued logic — unknown = unknown evaluates to unknown, not true. WHERE column = NULL silently returns zero rows every single time, with no error to tip you off. Always use IS NULL / IS NOT NULL instead.

NULL's Sequel: The NOT IN Trap

-- If ANY value in the list (or subquery result) is NULL, the ENTIRE NOT IN silently
-- matches zero rows — not an error, just wrong results:
SELECT name FROM dbo.Restaurant
WHERE cuisine NOT IN (SELECT cuisine FROM dbo.Restaurant WHERE cuisine IS NULL OR city = 'Dallas');
-- If that subquery returns even one NULL alongside real values, this returns nothing at all.

-- Safer: explicitly exclude NULLs from the list first, or use NOT EXISTS instead (Chapter 5)
SELECT name FROM dbo.Restaurant r
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.Restaurant r2 WHERE r2.city = 'Dallas' AND r2.cuisine = r.cuisine
);