How to Write Complex Search Queries in SQL?

We can write complex search conditions with WHERE clause as well as with HAVING clause. Let’s write down few complex queries to understand how to do that.

Let’s write down first search query using HAVING clause.

SELECT invoice_date, 
COUNT(*) AS invoice_quantity,
SUM(invoice_total) AS invoice_sum
FROM invoices
GROUP BY invoice_date
HAVING invoice_date BETWEEN '2017-02-01' AND '2017-02-28'
AND COUNT(*)>0
AND SUM(invoice_total)>500
ORDER BY invoice_date DESC;

In the query above, I am fetching invoice date and also calculating the total number of invoices and also calculating sum of invoice total for each date as I am also using group by on invoice dates.

Now, take a look at the HAVING clause. It has addition conditions as well. There are 3 conditions. First one states that invoice date must be between 2017-02-01 and 2017-02-28. In second conditions invoice count must be greater than 0 (which automatically will be anyway but just to show a condition I am using it). Third one states that the sum of invoice totals must be greater than $500.

Notice that second and third conditions in HAVING included aggregate functions.

The first condition doesn’t include any aggregate function so we it can be used with either WHERE or with HAVING. So, we can write down the same query as above using WHERE clause.

SELECT invoice_date, 
COUNT(*) AS invoice_quantity,
SUM(invoice_total) AS invoice_sum
FROM invoices
WHERE invoice_date BETWEEN '2017-02-01' AND '2017-02-28'
GROUP BY invoice_date
HAVING COUNT(*)>0
AND SUM(invoice_total)>500
ORDER BY invoice_date DESC;

You can checkout the DEMO from the link given below. Both of the queries return the same result set.

So, which one to use, HAVING or WHERE?

There is no rule of thumb but if you write down all your search or limit conditions in HAVING, it will be much easier to read rather than writing down one condition in WHERE and others in HAVING.

But, if you’re not using ny aggregate functions then you should code all your conditions in WHERE clause. That’s what I do.

However, there is a performance advantage of using HAVING since all the search conditions will be applied after grouping the rows while in WHERE the search conditions are applied before the rows are grouped.

It depends on different software you are using for your SQL query execution as well. Some software take care fo the performance, some don’t. So, at the end, I would say keep your code clean and easily readable and the performance will take care of itself.

Share this post