A WHERE clause filters rows: it keeps only the rows that satisfy one or more conditions. Use it when a full table is too broad—for example, to find pending orders, customers in one state, or sales from a particular period. Unlike LIMIT and OFFSET, which reduce the number of returned rows without finding particular values, WHERE selects rows by criteria. section 5.0
Write WHERE after FROM. A simple condition reads much like English: select a column from a table where another column equals a value. Use = for equality. Numeric values are written without currency symbols or commas; commas cause syntax errors. A numeric value such as 10000 and 10000.00 can return the same result when the stored values compare as equal. section 5.1.1
SELECT TitleName FROM title WHERE Advance = 10000.00;
This query returns title names only for rows whose Advance is exactly 10000.00. section 5.1.1
String values need single quotes, such as WHERE TitleName = 'Anne of Fact Tables'. The match must be exact: a missing letter, extra space, tab, or carriage return can prevent a row from being returned. Use ordinary SQL single quotes rather than smart quotes copied from formatted documents. section 5.1.2
Combine conditions when one test is not enough:
- Use AND when every condition must be true. Adding conditions narrows the result set because a row failing even one condition is excluded. section 5.2.1
- Use OR when either alternative may qualify, and use parentheses whenever AND and OR appear together to make the intended grouping explicit. section 5.2.3
- Place ORDER BY after WHERE when filtered results also need sorting. section 5.2.4
Grouping mixed conditions
To find titles priced at 9.95 that were either published on 2016-02-06 or had an advance of 5000, put the OR alternatives in parentheses. Without parentheses, AND takes priority over OR, so the query can return rows that do not meet the intended price condition. section 5.2.3
SELECT
TitleName,
Price,
PublicationDate,
Advance
FROM title
WHERE Price = 9.95
AND (PublicationDate = '2016-02-06'
OR Advance = 5000);
Parentheses make the two alternative conditions act as one requirement alongside Price = 9.95. section 5.2.3
WHERE also protects data-changing statements. In DELETE, it identifies the rows to remove; omitting it deletes every row in the table. Check the filtering condition carefully before executing a change. Avoid quoting numeric values unnecessarily: an RDBMS may convert types behind the scenes, which can hurt performance on larger data sets. section 17.2.1 section 5.4
