Filtering rows with WHERE

WHERE holds a condition that SQL Server tests against every row from the FROM clause. Rows where it is true are kept; the rest are dropped before the SELECT list is worked out.

1SELECT ProductId, Name, CategoryId, Price, Stock2FROM dbo.Products3WHERE Price <= 12.004  AND Stock > 0;

Comparing numbers, text and dates

1SELECT Name, Stock2FROM dbo.Products3WHERE Stock = 0;

Name              Stock
----------------  -----
Wall plugs (100)      0
Safety goggles        0

Text comparisons follow the column’s collation. The default on most servers is case-insensitive, so N'BAG' finds bag. Dates compare in calendar order, as long as the column is a real date type:

1SELECT Name, Unit, AddedOn2FROM dbo.Products3WHERE Unit = N'BAG' OR AddedOn >= '2023-06-01';

Name                Unit  AddedOn
------------------  ----  ----------
Cement 25 kg        bag   2023-03-10
Sand 25 kg          bag   2023-03-10
Masonry paint 10 L  tin   2023-06-12
Paint roller set    each  2023-06-12
Safety goggles      each  2023-07-01

AND, OR and the precedence trap

AND is true only when both sides are true; OR is true when either side is. When you mix them, AND is applied first, just as × comes before + in arithmetic. Say you want tools or paint, but only the expensive ones:

1-- Wrong: read as  CategoryId = 1  OR  (CategoryId = 5 AND Price > 20)2SELECT Name, CategoryId, Price3FROM dbo.Products4WHERE CategoryId = 1 OR CategoryId = 5 AND Price > 20;

Name                CategoryId  Price
------------------  ----------  -----
Claw hammer                  1  18.50
Cordless drill               1  89.00
Tape measure 8 m             1   9.75
Masonry paint 10 L           5  54.00

All three tools came back, cheap or not, because the price test was attached only to paint. Parentheses say what you mean:

1SELECT Name, CategoryId, Price2FROM dbo.Products3WHERE (CategoryId = 1 OR CategoryId = 5) AND Price > 20;

Name                CategoryId  Price
------------------  ----------  -----
Cordless drill               1  89.00
Masonry paint 10 L           5  54.00

IN and BETWEEN

IN is a short way to write several = tests joined by OR. BETWEEN is a range that includes both ends. Both have a NOT form.

1SELECT Name, CategoryId, Price2FROM dbo.Products3WHERE CategoryId IN (3, 4)4  AND Price BETWEEN 4.50 AND 11.90;

Name               CategoryId  Price
-----------------  ----------  -----
Timber 2x4 2.4 m            3   6.80
Wood screws (200)           4  11.90
Wall plugs (100)            4   4.50

Patterns with LIKE

1SELECT Name2FROM dbo.Products3WHERE Name LIKE N'[CT]%' AND Name NOT LIKE N'%kg';

Name
----------------
Claw hammer
Cordless drill
Tape measure 8 m
Timber 2x4 2.4 m

A pattern that starts with % forces SQL Server to check every row, because an index cannot help find text in the middle of a value. On a few thousand rows that is fine; on millions, it is the first thing to look at when a search is slow.

1SELECT Name, Price, Stock2FROM dbo.Products3WHERE CategoryId IN (3, 4)4  AND Price < 105  AND Stock > 0;

Name              Price  Stock
----------------  -----  -----
Timber 2x4 2.4 m   6.80    500