WHERE clause
The WHERE
clause specifies any conditions or filters to apply to your data. This allows you to select only a specific subset of the data. The WHERE
clause is used right after the FROM
clause.
Here is the basic syntax of a SELECT statement with the optional WHERE
clause:
SELECT column1, column2, columnN
FROM table_name
WHERE condition
Here, condition
is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be removed from the output. A row satisfies the condition if it returns true when the actual row values are substituted for any variable references.
Basic WHERE
clause example:
-- compute the average salary per department per job_title
-- filtering the result set only to include the departments and job titles with an average salary of at least $50,000
SELECT department, job_title, AVG(salary)
FROM employees
WHERE salary >= 50000
GROUP BY department, job_title;
This query results in a table with columns for department, job title, and average salary. Each row represents the average salary for a unique combination of department and job title, and only those groups with an average salary of at least $50,000 are returned.
Notice that the WHERE
clause comes before the GROUP BY
clause in this example because the WHERE
clause is used to filter the rows in a table before any aggregations are performed. In contrast, the HAVING
clause filters data after aggregations are performed.