From: Date: March 5 2003 5:46pm
Subject: Re: HAVING vs. WHERE
List-Archive: http://lists.mysql.com/mysql/134038
Message-Id: <p05200fbdba8bd8b06ba8@[192.168.1.33]>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

At 11:17 -0500 3/5/03, Jonathan Arnold wrote:
>In the MySQL reference, it warns against using HAVING for items
>that "should" be in a WHERE clause. I'm not sure what items
>"should" be in a WHERE clause.

If a test can be in either clause, put it in the WHERE clause.  The principle
is that the sooner you can eliminate rows from consideration for further
processing, the better. WHERE is evaluated to determine which rows to
select, HAVING is applied to the selected rows after that.

This mostly makes a difference when using HAVING with GROUP BY. If you can
use WHERE to prevent certain rows even from entering into the grouping
calculations, it saves work compared to eliminating them later in the
HAVING.

>
>My exact problem is I want to select some records that have a
>dotted IP address as one of the fields. And I want to filter out
>the multicast addresses, which are the addresses that begin with
>the numbers in the range of 224. thru 239. This does it:
>
>SELECT * FROM Client WHERE left(inetAdr,instr(inetAdr,"."))< 224
>     OR left(inetAdr,instr(inetAdr,".")) > 239
>
>and this works as well:
>
>SELECT *,left(inetAdr,instr(inetAdr,".")) as ia FROM Client HAVING ia < 224
>     OR ia > 239
>
>and it is a little cleaner, although as I'm going to be doing this in a
>PHP script, cleanliness isn't all that important.
>
>So I guess I have 2 questions:
>
>1] Which should I use?
>2] Is this the easiest way to check for the multicast address?
>
>--
>Jonathan Arnold     (mailto:jdarnold@stripped)
>Amazing Developments       http://www.buddydog.org
>
>It ain't what you don't know that gets you into trouble.
>It's what you know for sure that just ain't so.  Mark Twain