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.bru...@oracle.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
