YogeshChauhan . com

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

amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

SQL Inner JoinSQL/MySQLWordPress: How to get field values in Advanced Custom Fields?WordPressWordPress: How to loop through a repeater field in ACF?WordPressIntroduction to Angular modules Part 2: NgModules and componentsAngularWhat is a Strict Requirement in PHP 7 Function Declarations?PHPCONCAT and CONCAT_WS Functions in PostgresPostgres