List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:April 25 2011 4:43pm
Subject:Re: LEFT JOIN and WHERE: identical or not and what is better?
View as plain text  
Hi Andre, everybody!


Andre Polykanine 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...)?

In your subject line, you are mixing unrelated things:

- "LEFT JOIN" is an alternative to "INNER JOIN".
  It tell the database to return not only matching row combinations but
  also those where the first (left-hand) table has a row with a NULL
  column.

- "ON" is an alternative to "WHERE".

For both aspects, the manual has more information than I will be able to
think of in this mail.

So the real difference between your statements is not "LEFT JOIN" vs
"WHERE", or "ON" vs "WHERE", it is "LEFT JOIN" vs "inner join".
It will become important if you have rows in table "Blogs" whose column
"UserId" holds NULL rather than any definite value.

To understand that, you will have to read about NULL and the
three-valued logic of SQL (whose comparisons can return true, false, and
unknown).

In general, an inner join should be faster - but who cares?
SQL statements must be coded for correctness first, not for speed - and
that will determine your choice of "LEFT JOIN" vs "inner join".
Tuning and optimization come later (in priority).

The same holds for style etc: Clean programming is nice (and helpful in
understanding and maintenance), but correctness comes first.


Regards,
Joerg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
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