Andrus,

First of all, realize that there is an ANSI SQL-92 Standard language, and each database, Oracle, MS Sql-Server, FoxPro, Access, etc. uses a subset/superset of the standard language.

According to Joe Celko in SQL For Smarties, Second Edition, SQL builds a table using the specifications in the FROM and WHERE clauses, including grouping, then applies the HAVING requirements, then SELECTs certain columns from that result and applies the DISTINCT option, dropping extra rows. In the standard language, if there is no GROUP BY clause, the HAVING conditions are evaluated against the whole compiled (from FROM and WHERE) table.

HAVING is for things you don't know until you're almost done, like when you are looking for duplicate rows and code:

SELECT MyField, COUNT(*) AS NumRecs ; 
  FROM MyTable ;
  GROUP BY MyField ;
  HAVING NumRecs > 1

It might not look that different to you if you place your conditions in HAVING rather than in FROM or WHERE, but in complex queries against large databases there is a world of difference.


>So the only difference is that WHERE is rushmore optimizable but
>HAVING is NOT rushmore optimizable ?
>
>Why there is a such difference in VFP ?
>Can HAVING without group by used in all SQL servers ?
>
>>Andrus,
>>
>>Mark is right - you are better off using WHERE.
>>
>>WHERE selects only those records that meet the specified condition and retrieves only those records.
>>
>>HAVING retrieves all records and then throws away the records it doesn't need. That means you have the "cost" of bringing way too much data over the network.
>>

SELECT ;
>>    < complex expression > ;
>>    FROM MyTable ;
>>    WHERE BETWEEN(MyValue, 1, 1000) ;
>>    INTO CURSOR MyCursor ;
>>    ORDER BY MyOrder

>>
>>
>>>Using having without group by
>>>
>>>Is it OK to use having without group by in SQL SELECT ?
>>>
>>>I want to place additional filter to result columns,
>>>like:
>>>
>>>Select ;
>>> as myvalue, ;
>>> from mytable ;
>>> having betw( myvalue, 1,100 )
>>>
>>> is a very complex expression and i don't wish to include it to having clause second time.