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
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
