List:General Discussion« Previous MessageNext Message »
From:Suresh Kuna Date:April 26 2011 12:31pm
Subject:Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
View as plain text  
I would go with join rather than where condition.

2011/4/26 Halász Sándor <hsv@stripped>

> >>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
> 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...)?
> <<<<<<<<
>
> >>>> 2011/04/25 10:16 -0500, Johnny Withers >>>>
> 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.
>
> ...
>
> I prefer to write the INNER JOIN out though because it leaves my WHERE
> clause to do filtering.
> <<<<<<<<
> and it is usual to write all about the joining in the FROM-clause --the
> tables and the criterion for joining them-- and reserve the WHERE-clause for
> filtering the result:
>
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` =
> `Users`.`Id`
>
> SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` =
> `Users`.`Id`
>
> That is, if you already had a table with the joined outcome, you would use
> the WHERE-clause to determine what of it enters into further processing.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

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