List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:April 25 2011 3:16pm
Subject:Re: LEFT JOIN and WHERE: identical or not and what is better?
View as plain text  
The only difference once MySQL parses these two queries is the first one is
a LEFT JOIN, which will produce all records from the blogs table even if
there is no matching record in the users table. The second query produces an
INNER JOIN which means only rows with matching records in both tables will
be returned.

Which one is faster? Probably the second since NULLs do not have to be
considered -- probably not much faster though.
Which one is better? That'll depend on your needs, if you only need records
from both tables that have a matching row in the other, the second is
better. If you need all blogs, even those without a matching user (can that
even occur?), the first one is better.

I prefer to write the INNER JOIN out though because it leaves my WHERE
clause to do filtering.

JW


On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine <andre@stripped> wrote:

> Hello everyone,
> Sorry for my beginner question. Actually I have been using MySql for a
> long  time  but  I  just  start  using some advanced things (earlier I
> accomplished  those  tasks  with  PHP),  so  I  will  be asking stupid
> questions, please bear with me.
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM     `Blogs`     LEFT     JOIN     `Users`     ON
> `Blogs`.`UserId`=`Users`.`Id`;
> and the following one:
> SELECT       `Blogs`.*      FROM      `Blogs`,      `Users`      WHERE
> `Blogs`.`UserId`=`Users`.`Id`;
>
> 1. Are they identical?
> 2.  Which is better (faster, more optimal, more kosher, I mean, better
> style...)?
> Thanks!
>
>
> --
> With best regards from Ukraine,
> Andre
> Skype: Francophile
> Twitter: http://twitter.com/m_elensule
> Facebook: http://facebook.com/menelion
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
LEFT JOIN and WHERE: identical or not and what is better?Andre Polykanine25 Apr
  • Re: LEFT JOIN and WHERE: identical or not and what is better?Mitchell Maltenfort25 Apr
  • Re: LEFT JOIN and WHERE: identical or not and what is better?joao25 Apr
  • Re: LEFT JOIN and WHERE: identical or not and what is better?Johnny Withers25 Apr
  • Re: LEFT JOIN and WHERE: identical or not and what is better?Joerg Bruehe25 Apr
  • Re: LEFT JOIN and WHERE: identical or not and what is better?,etc.hsv26 Apr
    • Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.Suresh Kuna26 Apr
    • Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.Andre Polykanine26 Apr
      • Re: LEFT JOIN and WHERE: identical or not and what is better?,etc.hsv27 Apr