On Wed, Dec 12, 2012 at 8:25 AM, Shawn Green <shawn.l.gr...@oracle.com> wrote: > On 12/11/2012 7:22 PM, h...@tbbs.net wrote: >> >> ... (Are all the distinct "id"s really needed? When one joins on a >> >> field with the same name in both tables, one may use 'USING', and >> only the common field, with neither NULL, shows up in the output.) >> > > This is a perfectly acceptable naming convention to use. For example if you > have a field on the `art` table that references the ID column of the > `person` table to indicate the owner and another field to indicate the > person who created the art, you might want to use the names > `owner_person_id` and `artist_person_id` to keep them separate from the `id` > column used to uniquely identify the work of art itself. > > In this design pattern, each table has a numeric ID column (string-based > primary keys are perfectly legal but have their drawbacks and should be used > with care) and to reference it from another table you can use the pattern > <parenttable>_id. It keeps your naming conventions clean and predictable. > > If I were to try to use a USING operator in my opening example, I would be > trying to match the PK fields of two separate types of data. > > (the USING example) > SELECT ... FROM art INNER JOIN person USING(id)... > > Let's say that I renamed the id fields to art_id and person_id to make them > table-specific. This still fails because a person's identifier as an owner > is not the same as a work of art's creator. It also does not allow me to use > the `person` table more than once in a single query. > > (FAIL: a renamed USING example) > SELECT ... > FROM art > INNER JOIN person USING(person_id) <--- does this refer to the owner or the > creator of the art? > > (the name template example) > SELECT ... > FROM art > INNER JOIN person owner > on art.owner_person_id = owner.id > INNER JOIN person artist > on art.artist_person_id = artist.id > ... > > I admit I am a control freak when it comes to the accuracy of my queries, > the integrity of my data, and the ease of maintenance for my SQL statements. > Because of this, I much prefer the regular predictability of the ANSI JOIN > syntax (demonstrated above) for any JOIN except for an intentional Cartesian > product. For that case alone, I prefer a comma join > > (Cartesian product example) > SELECT ... > FROM table1, table2 > ... > > Not only is the ANSI syntax the only way to specify an OUTER join (such as > LEFT JOIN or RIGHT JOIN) it forces the author of the statement to recognize > that they do or do not have an ON clause for this table relationship. > Separating the same conditions into a WHERE clause makes it very easy to > overlook a table relationship and accidentally create a Cartesian product > which can often devastate query performance. It is perfectly legal to use > the comma-join syntax with MySQL but I strongly recommend against it just > because it can only be used for INNER joins or CROSS joins and because it > forces you to put your relationship conditions in the WHERE clause. > > Another strike (to me, anyway) against the comma join is that in order to > process joins more like the specifications in the SQL standards, we demoted > the precedence of the comma operator with 5.0.12. These changes also > affected the behavior of the USING and NATURAL JOIN operators. > http://dev.mysql.com/doc/refman/5.5/en/join.html
Thanks very much Shawn for this very informative post. I learned SQL in the early 1980's and I was not taught the JOIN syntax, only the comma join and WHERE. It's really just force of habit that I write queries that way. I will try very hard in the future to break that habit and use the JOIN syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
