WHERE in T-SQL: an overview

A WHERE clause keeps only the rows that match a condition, such as the customers in one country.

Novice · T-SQL · SQL · Updated

By

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';
CompanyName City
Around the Horn London
B's Beverages London
Consolidated Holdings London
Eastern Connection London
Island Trading Cowes
North/South London
Seven Seas Imports London

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#

SQL C#
Country = 'UK' country == "UK" SQL compares with a single =
'UK' "UK" SQL text goes in single quotes
'uk' matches 'UK' "uk" != "UK" SQL Server's comparisons usually ignore case

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.

Do it: find the customers in Germany

The sales team is planning a trip to Germany and wants a list of the customers there, sorted by city so the visits can be planned town by town.

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

CompanyName City Country
Alfreds Futterkiste Berlin Germany
Around the Horn London UK
Blauer See Delikatessen Mannheim Germany
Bon app' Marseille France
Königlich Essen Brandenburg Germany
Seven Seas Imports London UK

Your query should give exactly these rows, in this order:

CompanyName City
Alfreds Futterkiste Berlin
Königlich Essen Brandenburg
Blauer See Delikatessen Mannheim

Your job: write one SELECT query that lists the customers in Germany.

  • Show two columns, CompanyName and City, in that order.
  • Keep only the rows whose Country is 'Germany'.
  • Sort them by City, A to Z.

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

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

Cheat sheets