Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Feb 16th, 2012

 

As a basic rule of query building:

1. The WHERE clause should be used to limit the number of rows returned by or affected by the a SQL statement.

2. The GROUP BY clause is used to group a selected set of rows into a set of summary rows by values in one or more columns. The summary set of rows is required when using aggregate functions that provide information about each group by summary row.

3. The HAVING clause, can only be used under the SELECT clause and is used to limit the results of the GROUP BY rows or aggregate funtion results.

In SQL Server, the HAVING clause under a SELECT would function like a WHERE cluase if no GROUP BY statement was include, however this is no the case in Oracle, for example, where this would result in an error.

The big issue question for "Can we replace the WHERE clause with the GROUP BY clause?" is "Should we replace the WHERE clause with the GROUP BY clause?"

When forming a query, you should ask:

1) Is this query performing something other than a SELECT (ie. DELETE, MERGE, or UPDATE )?
(If the answer is YES, then you will always use the WHERE clause to limit data set)

2) If this query is a SELECT, then:
a) Do you require the data to be filtered?
(If the answer is YES, then use a WHERE clause)

b) Does the data set being returned contain an aggregate funtion?
(If the answer is YES, then you will require a GROUP BY clause to summarize the data set for the aggregate funtions)

c) Do you have summarized data, and do you require the results of the summarized data be filtered more?
(If the answer is YES, then you will use the HAVING clause to filter the summarized data set)

Thus, for the example query:

select * from table_name where some_condition

Should we replace the WHERE clause with a GROUP BY clause? No.

Can we replace the WHERE clause with a GROUP BY clause? ...well, Yes, No, and Maybe. In any case it would not be a good thing to do, and you really would be setting yourself up to be subject to the Leonard Hofstadter Rule (As stated by the character Leonard Hofstadter in the TV series, The Big Bang Theory).

The Leonard Hofstadter Rule:
"You are attached to another object by an inclined plane, wrapped helically around an axis."

First, lets just look at the core of the statement:

Select *
From table_name

Now to use the GROUP BY statement you would have to replace the " * " with the name of ever column in table_name.

Select column_1, column_2, ... column_N
From table_name

Now, are any of the columns in table_name of text, ntext, and image data type? Yes, then see The Leonard Hofstadter Rule, and the answer is no you can not replace the WHERE clause with a GROUP BY clause.

Next has table_name NOT been normalized? Say, for example, that table_name is a dump table containing values inserted into it with no PK and the columns contain repeating data, then I refer you back to The Leonard Hofstadter Rule, and the answer is no you can not replace the WHERE clause with a GROUP BY clause.

Next, lets say that table_name is normalized to 3NF, but its HUGE, MASSIVE, etc ... then I refer you back to The Leonard Hofstadter Rule, and the answer is ... well, while yes you can replace the WHERE clause with a GROUP BY clause, the resulting query would eat up a lot of time. The reason for this is the result of the way that the WHERE clause and the GROUP BY clause function.

The query "select * from table_name where some_condition" with the WHERE clause works something like this:

"I only want to look at data in table table_name that meets some_condition, and return all the columns of data from that data set"

The GROUP BY / HAVING works like this:

"Give me ALL the data in the table, then go back and group ALL that data by groups having some_condition."

As you can see, for anything but small tables the query with GROUP BY / HAVING could cause a performance problem.

So, the answer to the interviewer would be, "Yes, for some limited select queries it would be possible to replace the WHERE clause with a GROUP BY / HAVING clause, however doing so would not be a BEST PRACTICE to follow."