List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:February 3 2009 6:02pm
Subject:RE: WHERE vs. ON
View as plain text  
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: jschwartz@stripped
> To: mysql@stripped
> 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
Thread
WHERE vs. ONJerry Schwartz3 Feb
  • RE: WHERE vs. ONMartin Gainty3 Feb
    • RE: WHERE vs. ONJerry Schwartz3 Feb
      • Re: WHERE vs. ONRob Wultsch3 Feb
  • Re: WHERE vs. ONPerrin Harkins3 Feb
  • Re: WHERE vs. ONBaron Schwartz4 Feb
    • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONJohn Daisley4 Feb
      • RE: WHERE vs. ONJerry Schwartz4 Feb
    • Re: WHERE vs. ONClaudio Nanni4 Feb
  • Re: WHERE vs. ONMartijn Tonies4 Feb