Filtering narrows a broad query result to rows that meet one or more conditions. It is how you ask for pending orders, recent sales, or customers in a particular place instead of returning every row. In SQL, filtering is usually written in a WHERE clause after FROM—much like adding “where” near the end of an English request. section 5.0 section 5.1.1
A condition compares a column with a value. To find titles whose author advance is exactly 10,000, use =. Numeric values are written without currency symbols or commas; commas cause syntax errors. Numeric precision can differ in appearance—such as 10000 and 10000.00—while still matching the same stored number. section 5.1.1
SELECT TitleName FROM title WHERE Advance = 10000.00;
This query returns only TitleName values from rows whose Advance equals 10000.00. Add Advance to SELECT when you want to check the value that made each row match. section 5.1.1
Combine conditions to control how restrictive a filter is:
- Use AND when every condition must be true. Each added condition can reduce the result set; a row that fails even one condition is excluded. section 5.2.1
- Use OR when any condition may be true. Added alternatives can keep the same number of rows or increase the result set. section 5.2.2
- Use range operators such as >, <, and BETWEEN for numeric or date ranges; string-range searches can vary with collation, case, and special characters. section 7.1
Refining a title search
To find titles with both a 5,000 advance and a 15% royalty, connect the conditions with AND. Replacing AND with OR changes the question: now a title can match either the advance or royalty condition, so more rows may be returned. section 5.2.1 section 5.2.2
Filtering remains useful beyond a single table. A WHERE condition can limit rows after JOINs, and keeping filtering predicates separate from join predicates generally improves readability. Filtering early can also leave fewer rows to join, reducing the work required. Variables make recurring filters easier to change, such as a title name, date range, or sales threshold. section 8.6 section 13.2 section 13.3.1
Use filtering with care when changing data. In DELETE, a WHERE clause identifies the intended rows; omitting it deletes every row in the table. Filters also work when selecting from views, where they can restrict a view’s rows just as they restrict table rows. section 17.2.1 section 20.1.2
