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, 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.


Most Read

#1 How to add Read More Read Less Button using JavaScript? #2 How to check if radio button is checked or not using JavaScript? #3 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
What is a Strict Requirement in PHP 7 Function Declarations?PHPHow to add menu to your WordPress theme?WordPressHow to use @supports rule in CSS?CSSWhat are Modules and Components in Angular?AngularConfiguring Modules with @forward rule in SCSS (Sass)SCSSEXISTS and NOT EXISTS in PostgresPostgres