WHERE in T-SQL: an overview

After this lesson you can

• Filter rows with a WHERE clause

Key takeaways

• WHERE keeps only the rows where its condition is true, and it goes after FROM and before ORDER BY.
• SQL compares with a single = and puts text in single quotes: WHERE Country = 'UK'.
• SQL Server's comparisons usually ignore case, so 'uk' matches 'UK'.
• Numbers need no quotes: WHERE CategoryId = 2.

A table holds every row; usually you want only some of them. The WHERE clause adds a filter: only the rows that match its condition are returned.

SELECT CompanyName, City
FROM Customers
WHERE Country = 'UK';

Of 91 customers, 7 are in the UK.

How it works

The database looks at each row in turn and checks the condition. Rows where it's true are kept; the rest are left out. It's the if statement of SQL, applied to every row.

WHERE goes after FROM and before ORDER BY:

SELECT CompanyName, City
FROM Customers
WHERE Country = 'UK'
ORDER BY City, CompanyName;

Three things that differ from C#

That last one depends on how the database was set up (its collation), but SQL Server's default ignores case, so WHERE Country = 'uk' finds the same 7 customers.

Numbers need no quotes

Numbers are written as they are:

SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryId = 2;

That returns the 12 products in category 2, Condiments.

Check yourself

1 question to try on the lesson page.

So what

Almost every query an app runs has a WHERE: this customer's orders, this week's sales, this product. Filtering in the database means only the rows you need ever reach your code.

Key terms

• WHERE
• Comparison Predicate

Cheat sheets

• WHERE in T-SQL: an overview