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
