Overview
This lecture explains the important differences between the WHERE and HAVING statements in SQL, highlighting their functions, when to use each, and how they can be combined.
WHERE Statement
- The WHERE statement filters rows from the raw data before any grouping or aggregation occurs.
- Used to select only the rows that meet specified criteria (e.g., GPA > 2.5).
- Excludes individual data rows that do not satisfy the given condition from the query output.
HAVING Statement
- The HAVING statement is used in combination with GROUP BY to filter grouped (aggregated) results.
- Cannot be used without a GROUP BY statement.
- HAVING filters the aggregated results, such as groups whose average GPA exceeds a certain value.
- Example: After grouping by gender, HAVING can exclude groups with an average GPA less than 3.1.
Group By and Aggregation Examples
- GROUP BY gender or year splits the data into groups and calculates an aggregate (like average GPA) for each group.
- HAVING determines which of these aggregated groups appear in the final results based on a condition.
- Example: GROUP BY year with HAVING average GPA > 3.0 will only show the years meeting this threshold.
Combining WHERE and HAVING
- WHERE can be used to filter raw data before grouping, and HAVING can further filter the grouped results.
- Example: WHERE GPA > 2.5 removes low-GPA students, then GROUP BY and HAVING average GPA > 3.5 filters on grouped averages.
- Combined use allows precise control over which data is analyzed and which results are reported.
Key Terms & Definitions
- WHERE statement — Filters individual rows from raw data based on specified criteria before grouping or aggregation.
- HAVING statement — Filters groups of data after aggregation, used only with GROUP BY.
- GROUP BY — SQL clause that arranges data into groups based on one or more columns for aggregation.
- Aggregation — Combining multiple rows’ data into a summary value (e.g., average, sum, count).
Action Items / Next Steps
- Practice writing SQL queries using WHERE, GROUP BY, and HAVING for different datasets.
- Prepare examples showing how the results change using WHERE vs HAVING filters.
- Review course materials or readings on SQL filtering and aggregation.