List:General Discussion« Previous MessageNext Message »
From:Andre Polykanine Date:April 26 2011 2:55pm
Subject:Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
View as plain text  
Hello Halбsz,

            Aha. So, I should write
SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId`
instead of my original WHERE clause?
Thanks!

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

------------ Original message ------------
From: Halбsz Sбndor <hsv@stripped>
To: Andre Polykanine
Date created: , 7:00:03 AM
Subject: LEFT JOIN and WHERE: identical or not and what is better?, etc.


      >>>> 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.


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