List:General Discussion« Previous MessageNext Message »
From:Larry Martell Date:December 12 2012 5:23pm
Subject:Re: Help with left outer join
View as plain text  
On Wed, Dec 12, 2012 at 8:25 AM, Shawn Green <shawn.l.green@stripped> wrote:
> 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

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.
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