Structured Query Language (SQL) has several clauses to filter results in a data set. WHERE and HAVING are two examples of filters available to developers, but the one you use depends on the type of SQL statement. WHERE should be used on individual rows and HAVING should be used where results are grouped using aggregate functions.
What Is WHERE in SQL?
The WHERE clause filters data based on your defined parameters matching a single row. For example, if you have 1,000 rows in a data set, the WHERE clause examines each of the 1,000 rows and compares them to your defined parameters. The following is an example of a SQL statement using the WHERE clause:
SELECT first_name, last_name FROM Customer WHERE first_name=’john’
In the example above, every row in the Customer table is evaluated and only rows containing ‘john’ in the first_name column are returned. The data set returned displays every first name and last name of customers with a first name of “john.”
What Is HAVING in SQL?
The HAVING clause also filters rows from a data set, but it evaluates grouped results instead of individual rows. With aggregate SQL commands, similar rows are grouped together and each group is presented as a single row. For example, the following SQL command groups all customers by similar last names:
SELECT first_name, last_name FROM Customer GROUP BY last_name
In the example above, the data set groups all customers by last name, so all customers with the last name of “smith” will show up as a single row, and any customers with the last name of “doe” will display as a single row. If all your customers have a last name of “smith” and “doe,” the SQL data set will only contain two rows with a count for the number of customers grouped in the row.
The HAVING clause filters grouped records. SQL has several aggregate functions, but HAVING is coupled with GROUP BY. As an example, the following SQL statement filters the above statement and only returns grouped records where the last name is “smith”:
SELECT first_name, last_name FROM Customer GROUP BY last_name HAVING last_name=’smith’
What Is Aggregation in SQL?
Aggregate functions in SQL take several rows, perform calculations, and then return a single row in the returned data set. The GROUP BY function returns grouped records based on the column parameter included in your SQL statement. SQL has several aggregate functions, including COUNT, SUM, AVG, MIN, and MAX.
Both the WHERE and HAVING clause can be used with aggregate statements, but they have different functions. The WHERE clause evaluates all rows, and the HAVING clause evaluates rows after they’ve been aggregated in the data set.
WHERE vs. HAVING: Differences
Very few SQL queries should return all records from a database. With any application, developers need a way to return only the records necessary for their applications to function. The WHERE clause can filter a data set down to one result from millions of table records. The HAVING clause can also filter rows, but it must be used with aggregation. The WHERE clause doesn’t require any grouping, but you need to use the GROUP BY operation to work with the HAVING clause.
HAVING or WHERE: Which to Use and When
For standard SQL queries, WHERE is the primary filter. Developers use the WHERE clause to select only defined rows. The SQL database server then returns only the rows that match the parameters defined in the WHERE clause. The following SQL query retrieves customers with a first name of “john”:
SELECT first_name, last_name FROM Customer WHERE first_name=’john’
HAVING works only on aggregate functions, so you use it when you need to make calculations or group rows and then filter the aggregate results. For example, the following SQL query retrieves customers with orders over $100:
SELECT first_name, last_name FROM Customer GROUP BY first_name, last_name HAVING SUM(order_total) > 100
In the first query, the WHERE clause looks at each record in the Customer table and retrieves rows where the first_name column contains the “john” value. In the second query, every customer’s orders are summed and only if the total_order sum is greater than 100 is it returned in the data set.
HAVING or WHERE in SQL: Which Is Faster?
If at all possible, always use the WHERE clause in your SQL queries. The WHERE clause is faster than HAVING. Because you need to group records before the HAVING clause can execute, it’s a slower strategy than filtering records with the WHERE clause. Some queries will require using HAVING, but it should only be used if the WHERE clause will not give you the desired result.
Related reading: SQL DELETE vs TRUNCATE
