Yogesh Chauhan's Blog

How to Write Complex Search Queries in SQL?

in SQL/MySQL on January 3, 2020

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, what should I do then? 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. 🙂


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Introduction to components and templates Part 4: Pipes and DirectivesAngularUseful (and probably ignored) HTML tags: Part 1HTMLPHP Variables ScopePHPSelector Lists and Combinators in SCSS (Sass)SCSSWhat is React? Learn the basicsReactHow to create a pricing table using flex in CSS?CSS