YogeshChauhan.com
How to Write Complex Search Queries in SQL?
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. 🙂

dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS

You might also like these

The Drupal flowDrupal3 JavaScript methods to use when you want to go back and forth in historyJavaScriptaddcslashes() and addslashes() String Functions in PHPPHPHow to find the HCF or GCD and LCM of two given numbers using Swift?SwiftHow to Recognize an Array in JavaScript?JavaScriptHow to use HTML picture Tag?HTML