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
After this lesson you can
- Filter rows with a WHERE clause
Key takeaways
WHEREkeeps only the rows where its condition is true, and it goes afterFROMand beforeORDER 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,
CompanyNameandCity, in that order. - Keep only the rows whose
Countryis'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.