List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:December 12 2012 1:25pm
Subject:Re: Help with left outer join
View as plain text  
On 12/11/2012 7:22 PM, hsv@stripped 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


Thread
Help with left outer joinLarry Martell11 Dec
  • Re: Help with left outer joinPeter Brawley11 Dec
    • Re: Help with left outer joinLarry Martell11 Dec
      • Re: Help with left outer joinPeter Brawley12 Dec
        • Re: Help with left outer joinLarry Martell12 Dec
  • Re: Help with left outer joinhsv12 Dec
    • Re: Help with left outer joinShawn Green12 Dec
      • Re: Help with left outer joinLarry Martell12 Dec
      • Re: Foreign-key naminghsv12 Dec
    • Re: Help with left outer joinLarry Martell12 Dec