Selection criteria arguments are used in the WHERE clause of SELECT, DELETE, and UPDATE statements, in the HAVING clause of SELECT statements, and in the ON clause of joined tables. Selection criteria consist of a collection of predicates and expressions that determine which rows of a table (or tables) will be returned by a SELECT statement, or which rows of a table will be affected by a DELETE or UPDATE statement.
WHEREA WHERE clause specifies record selection criteria for DELETE, SELECT, and UPDATE statements. Records that satisfy the selection criteria in a WHERE clause are returned by a SELECT statement. Records that satisfy the selection criteria in a DELETE or UPDATE WHERE clause are deleted from a table, or updated, respectively.
HAVINGThe HAVING clause disqualifies groups of records that otherwise satisfied selection criteria specified in the WHERE clause. Typically, the HAVING clause is used in conjunction with the GROUP BY clause, but the HAVING clause may be used on its own. If the HAVING clause is used with the GROUP BY clause, it must follow the GROUP BY clause in the SELECT statement. If no GROUP BY clause is included in the statement, the HAVING clause treats the entire table as a single group. The HAVING clause selection criteria must address group properties, and therefore must utilize expressions that either return single values (that is, aggregate functions), or have the same value in every row of the group (that is, grouped columns).
Consider the following example:
SELECT party, AVG(age) FROM voter
GROUP BY party
HAVING AVG(age) < 35;This query returns rows from the voter table containing political party affiliation (party) and average age. The result is grouped according to the party column. Entire groups of records are excluded from the query result if the average function, AVG, evaluated for a particular party on the age column produces a value less than 35.