Types of Filters

Filter numbers and dates with comparison operators, text with LIKE patterns, lists with IN, ranges with BETWEEN, and missing values with IS NULL.

Apprentice · T-SQL · SQL · Updated

By

After this lesson you can

  • Filter rows with comparisons, LIKE, IN, BETWEEN and IS NULL

Key takeaways

  • Compare numbers and dates with =, <>, >, >=, < and <=; write dates in single quotes, year first, as in '2016-07-08'.
  • LIKE matches text against a pattern: % stands for any number of characters, even none, and _ for exactly one.
  • IN matches any value in a list, and BETWEEN matches a range, including both ends.
  • NULL means unknown, so = NULL is never true: use IS NULL or IS NOT NULL.

Which filter you use depends on the kind of column, and the kind of question.

Comparing numbers and dates

Operator Means
= equal to
<> or != not equal to
> and >= greater than, greater than or equal to
< and <= less than, less than or equal to
-- products that cost $50 or more
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice >= 50;

That returns 7 products, from Côte de Blaye at $263.50 down to Manjimup Dried Apples at $53.00.

Dates are written in single quotes, year first: '2016-07-08'. Then they compare like numbers, earlier being smaller:

-- orders placed on July 8, 2016
SELECT OrderId, CustomerId, OrderDate
FROM Orders
WHERE OrderDate = '2016-07-08';

-- orders placed in 2018 or later
SELECT OrderId, OrderDate
FROM Orders
WHERE OrderDate >= '2018-01-01';

The first returns two orders, 10250 and 10251. The second returns 270.

Patterns in text: LIKE

LIKE matches text against a pattern, with two wildcards:

Wildcard Matches
% any number of characters, even none
_ exactly one character
-- employees whose first name starts with A
SELECT FirstName, LastName
FROM Employees
WHERE FirstName LIKE 'A%';
FirstName LastName
Andrew Fuller
Anne Dodsworth
Pattern Finds
'A%' starts with A
'%s' ends with s
'%Sauce%' contains Sauce anywhere
'____' exactly four characters
'The % of %' starts with "The ", and has " of " later, as in The Lord of the Rings
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%Sauce%';

That finds Louisiana Fiery Hot Pepper Sauce and Northwoods Cranberry Sauce.

A list of values: IN

IN matches any value in a list. It's shorter than writing = several times:

SELECT CompanyName, Country
FROM Customers
WHERE Country IN ('Spain', 'Portugal');

That returns 7 customers: 5 in Spain and 2 in Portugal.

A range: BETWEEN

BETWEEN matches a range, including both ends:

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice BETWEEN 10 AND 20;

That's the same as UnitPrice >= 10 AND UnitPrice <= 20, and it returns 29 products.

Missing values: IS NULL

When a column has no value, it holds NULL: not zero, not an empty string, but unknown. Many Northwind customers have no Region. Because NULL is unknown, = NULL is never true. Use IS NULL and IS NOT NULL instead:

SELECT CompanyName, Country
FROM Customers
WHERE Region IS NULL;

That returns 60 customers. WHERE Region = NULL would return none.

Do it: find the orders placed in July 2016

The accountant is closing the books for July 2016, and needs every order placed that month: from July 1 through July 31, both days included.

The Orders table (the Tables tab shows the SQL that makes it) has these rows:

OrderId CustomerId OrderDate
10248 VINET 2016-07-04
10249 TOMSP 2016-07-05
10250 HANAR 2016-07-08
10251 VICTE 2016-07-08
10269 WHITC 2016-07-31
10270 WARTH 2016-08-01
10271 SPLIR 2016-08-01

Order 10269, on July 31, is in the month; orders 10270 and 10271, on August 1, aren't. Your query should give exactly these rows, in any order:

OrderId OrderDate
10248 2016-07-04
10249 2016-07-05
10250 2016-07-08
10251 2016-07-08
10269 2016-07-31

Your job: add a filter to the query in the box so it lists July's orders.

  • Keep its two columns: OrderId and OrderDate.
  • Keep only the orders whose OrderDate is from '2016-07-01' to '2016-07-31', both days included.

Practiced live on the lesson page: write the SQL right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

Comparisons, LIKE, IN, BETWEEN and IS NULL cover nearly every filter you'll write: pick the one that matches the kind of question you're asking.

Key terms

Cheat sheets