ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause...
Martin ______________________________________________ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > From: jschwa...@the-infoshop.com > To: mysql@lists.mysql.com > Subject: WHERE vs. ON > Date: Tue, 3 Feb 2009 12:24:52 -0500 > > Somebody, I think it was somebody from MySQL, said that you should never put > anything into a WHERE clause that could be put into the ON clause of a JOIN. > My guess is that this helps with the optimization, but it seems > counter-intuitive to me. I've never followed that advice, but I'm starting > to experiment with it. My first, rather simple example didn't seem to > accomplish much: > > > > giiexpress.com: mysql>explain > > -> SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc > > -> FROM pub JOIN prod ON pub.pub_id = prod.pub_id > > -> WHERE pub.pub_code = 'dc' > > -> AND prod.prod_discont = 0 > > -> AND prod.prod_samp_doc IS NOT NULL > > -> ORDER BY prod.prod_num\G > > *************************** 1. row *************************** > > id: 1 > > select_type: SIMPLE > > table: pub > > type: ALL > > possible_keys: PRIMARY > > key: NULL > > key_len: NULL > > ref: NULL > > rows: 652 > > Extra: Using where; Using temporary; Using filesort > > *************************** 2. row *************************** > > id: 1 > > select_type: SIMPLE > > table: prod > > type: ref > > possible_keys: pub_id > > key: pub_id > > key_len: 46 > > ref: giiexpr_db.pub.pub_id > > rows: 112 > > Extra: Using where > > 2 rows in set (0.25 sec) > > > > > > giiexpress.com: mysql>explain > > -> SELECT prod.prod_num, prod.prod_title, prod.prod_samp_doc > > -> FROM pub JOIN prod ON pub.pub_id = prod.pub_id > > -> AND pub.pub_code = 'dc' > > -> WHERE prod.prod_discont = 0 > > -> AND prod.prod_samp_doc IS NOT NULL > > -> ORDER BY prod.prod_num\G > > *************************** 1. row *************************** > > id: 1 > > select_type: SIMPLE > > table: pub > > type: ALL > > possible_keys: PRIMARY > > key: NULL > > key_len: NULL > > ref: NULL > > rows: 652 > > Extra: Using where; Using temporary; Using filesort > > *************************** 2. row *************************** > > id: 1 > > select_type: SIMPLE > > table: prod > > type: ref > > possible_keys: pub_id > > key: pub_id > > key_len: 46 > > ref: giiexpr_db.pub.pub_id > > rows: 112 > > Extra: Using where > > 2 rows in set (0.06 sec) > > > > Are the results the same because my query is too simple? > > > > Regards, > > > > Jerry Schwartz > > The Infoshop by Global Information Incorporated > > 195 Farmington Ave. > > Farmington, CT 06032 > > > > 860.674.8796 / FAX: 860.674.8341 > > > > <http://www.the-infoshop.com> www.the-infoshop.com > > <http://www.giiexpress.com> www.giiexpress.com > > www.etudes-marche.com > > > _________________________________________________________________ Windows Live™ Hotmail®…more than just e-mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_012009
