Re: 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
I would go with join rather than where condition. 2011/4/26 Halász Sándor h...@tbbs.net 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=sureshkumar...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
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 h...@tbbs.net 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?, etc.
2011/04/26 17:55 +0300, Andre Polykanine Aha. So, I should write SELECT `Blogs`.* INNER JOIN `Users` ON `Users`.`Id`=`Blogs`.`UserId` instead of my original WHERE clause? Thanks! I think so. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LEFT JOIN and WHERE: identical or not and what is better?
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...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
'where' is a filter. You're limiting records based on a criterion. 'on' is used for joining. On Mon, Apr 25, 2011 at 10:42 AM, Andre Polykanine an...@oire.org 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...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mmal...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
I can be wrong about that, but I think the difference between them should be irrelevant so it makes me think about a paranoiac thought. For me, the only difference is: Chose the one you feel better to understand your code. Am I wrong or not? -- João Cândido de Souza Neto Andre Polykanine an...@oire.org escreveu na mensagem news:199779304.20110425174...@oire.org... 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...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LEFT JOIN and WHERE: identical or not and what is better?
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. Which one is faster? Probably the second since NULLs do not have to be considered -- probably not much faster though. Which one is better? That'll depend on your needs, if you only need records from both tables that have a matching row in the other, the second is better. If you need all blogs, even those without a matching user (can that even occur?), the first one is better. I prefer to write the INNER JOIN out though because it leaves my WHERE clause to do filtering. JW On Mon, Apr 25, 2011 at 9:42 AM, Andre Polykanine an...@oire.org 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...)? Thanks! -- With best regards from Ukraine, Andre Skype: Francophile Twitter: http://twitter.com/m_elensule Facebook: http://facebook.com/menelion -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LEFT JOIN and WHERE: identical or not and what is better?
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
