Replace Where clause by Having clause

Question

Hi Ques Can we replace Where clause by Having clause ?

Satyapriya Nayak · Answer

Hi Arun 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. Refer http://www.geekinterview.com/question_details/87837 Thanks