From: Martin Gainty
Date: February 3 2009 6:02pm
Subject: RE: WHERE vs. ON
List-Archive: http://lists.mysql.com/mysql/216142
Message-Id: <BLU142-W1B274056974355502AF23AEC20@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_4932996c-91e4-4d73-a101-b001ffaa33a2_"

--_4932996c-91e4-4d73-a101-b001ffaa33a2_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable


ON condition uses the same columnname from both source and target tables

whereas any column expressions can go in the WHERE clause...

Martin=20
______________________________________________=20
Disclaimer and confidentiality note=20
Everything in this e-mail and any attachments relates to the official busin=
ess of Sender. This transmission is of a confidential nature and Sender doe=
s not endorse distribution to any party other than intended recipient. Send=
er does not necessarily endorse content contained within this transmission.=
=20




> From: jschwartz@stripped
> To: mysql@stripped
> Subject: WHERE vs. ON
> Date: Tue=2C 3 Feb 2009 12:24:52 -0500
>=20
> Somebody=2C I think it was somebody from MySQL=2C said that you should ne=
ver put
> anything into a WHERE clause that could be put into the ON clause of a JO=
IN.
> My guess is that this helps with the optimization=2C but it seems
> counter-intuitive to me. I've never followed that advice=2C but I'm start=
ing
> to experiment with it. My first=2C rather simple example didn't seem to
> accomplish much:
>=20
> =20
>=20
> giiexpress.com: mysql>explain
>=20
>     -> SELECT prod.prod_num=2C prod.prod_title=2C prod.prod_samp_doc
>=20
>     -> FROM pub JOIN prod ON pub.pub_id =3D prod.pub_id
>=20
>     -> WHERE pub.pub_code =3D 'dc'
>=20
>     -> AND prod.prod_discont =3D 0
>=20
>     -> AND prod.prod_samp_doc IS NOT NULL
>=20
>     -> ORDER BY prod.prod_num\G
>=20
> *************************** 1. row ***************************
>=20
>            id: 1
>=20
>   select_type: SIMPLE
>=20
>         table: pub
>=20
>          type: ALL
>=20
> possible_keys: PRIMARY
>=20
>           key: NULL
>=20
>       key_len: NULL
>=20
>           ref: NULL
>=20
>          rows: 652
>=20
>         Extra: Using where=3B Using temporary=3B Using filesort
>=20
> *************************** 2. row ***************************
>=20
>            id: 1
>=20
>   select_type: SIMPLE
>=20
>         table: prod
>=20
>          type: ref
>=20
> possible_keys: pub_id
>=20
>           key: pub_id
>=20
>       key_len: 46
>=20
>           ref: giiexpr_db.pub.pub_id
>=20
>          rows: 112
>=20
>         Extra: Using where
>=20
> 2 rows in set (0.25 sec)
>=20
> =20
>=20
> =20
>=20
> giiexpress.com: mysql>explain
>=20
>     -> SELECT prod.prod_num=2C prod.prod_title=2C prod.prod_samp_doc
>=20
>     -> FROM pub JOIN prod ON pub.pub_id =3D prod.pub_id
>=20
>     -> AND pub.pub_code =3D 'dc'
>=20
>     -> WHERE prod.prod_discont =3D 0
>=20
>     -> AND prod.prod_samp_doc IS NOT NULL
>=20
>     -> ORDER BY prod.prod_num\G
>=20
> *************************** 1. row ***************************
>=20
>            id: 1
>=20
>   select_type: SIMPLE
>=20
>         table: pub
>=20
>          type: ALL
>=20
> possible_keys: PRIMARY
>=20
>           key: NULL
>=20
>       key_len: NULL
>=20
>           ref: NULL
>=20
>          rows: 652
>=20
>         Extra: Using where=3B Using temporary=3B Using filesort
>=20
> *************************** 2. row ***************************
>=20
>            id: 1
>=20
>   select_type: SIMPLE
>=20
>         table: prod
>=20
>          type: ref
>=20
> possible_keys: pub_id
>=20
>           key: pub_id
>=20
>       key_len: 46
>=20
>           ref: giiexpr_db.pub.pub_id
>=20
>          rows: 112
>=20
>         Extra: Using where
>=20
> 2 rows in set (0.06 sec)
>=20
> =20
>=20
> Are the results the same because my query is too simple?
>=20
> =20
>=20
> Regards=2C
>=20
> =20
>=20
> Jerry Schwartz
>=20
> The Infoshop by Global Information Incorporated
>=20
> 195 Farmington Ave.
>=20
> Farmington=2C CT 06032
>=20
> =20
>=20
> 860.674.8796 / FAX: 860.674.8341
>=20
> =20
>=20
>  <http://www.the-infoshop.com> www.the-infoshop.com
>=20
>  <http://www.giiexpress.com> www.giiexpress.com
>=20
> www.etudes-marche.com
>=20
> =20
>=20

_________________________________________________________________
Windows Live=99 Hotmail=AE=85more than just e-mail.=20
http://windowslive.com/howitworks?ocid=3DTXT_TAGLM_WL_t2_hm_justgotbetter_h=
owitworks_012009=

--_4932996c-91e4-4d73-a101-b001ffaa33a2_--