Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Unanswered: Is there a point to not using joins?

    Is there a point to not using joins?

    Example:
    Code:
    Select *
    from tbl_a a, tbl_b b
    where a.uid = b.uid
    versus
    Code:
    select *
    from tbl_a a inner join tbl_b b on a.uid = b.uid
    When I had switched a lot of queries to use joins, I noticed that there was a significant boost in how the query is handled.

    But does it come at a cost?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    JOIN syntax comes at a cost? no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where syntax is supported for legacy reasons, join is the more recent ISO/ANSI standard SQL.

    I doubt where will be dropped as a mechanism of associating tables but it pays to be with the current standard rather than the old standard if all you SQL will run on the newer standard
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You were asking the wrong question: Both versions you posted are joins. The first is a so-called implicit join and the latter an explicit join. Both are standardized in ISO/IEC 9075:2003, i.e. the most recent SQL standard.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by healdem
    where syntax is supported for legacy reasons, join is the more recent ISO/ANSI standard SQL.

    I doubt where will be dropped as a mechanism of associating tables but it pays to be with the current standard rather than the old standard if all you SQL will run on the newer standard
    It's not a question of being the newer standard or all that. To be relationally complete, you have to support a cartesian product. If you support that, you implicitly support the "old" syntax.

    Look at it this way: so you were running and old machine and you had to multiply two integers like so:

    unsigned multiply(unsigned a, unsigned b) {
    unsigned c = 0;
    while(a--) {
    c = c + b;
    }
    return c;
    }

    Now, on the old 6502 chips (remember the C-64?) there was no multiply instruction, so rolling your own isn't all that farfetched. (Of course, you wouldn't do it in C, but this is supposed to be illustrative...)

    But just because you now have a built in multiply instruction doesn't mean you're going to ditch addition. It's part of arithmetic, you have to have it.

    In the same way, the "cartesian product and filter" way of doing joins is dated, but any proper DBMS is going to support it because it's part of the algebra.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by sco08y
    It's not a question of being the newer standard or all that. To be relationally complete, you have to support a cartesian product.
    Also for a (full) cartesian product, one can either use the "old" (implicit) join syntax or the explicit
    Code:
    SELECT *
    FROM t1 CROSS JOIN t1
    So, if you like to, you can systematically use the explicit JOIN syntax everywhere, and forget about commas in FROM.
    (As a matter of fact, I always do: it's far better for readability and maintainability.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I see no reason for constructing SQL joins using the old and now out dated syntax of specifying the list of sets to be joined in the from clause separated by commas, and including the join condition in the where clause of the query. The problem with this approach is that it is hard to read, it causes confusion and most importantly, it does not properly convey the intent of the query. Yes, it can be done without JOIN syntax, but readers of the query must then search through the many lines of conditional syntax to determine which expression relates to a single set and which relates to multiple sets, simulating the behaviour of the join operator.

    I remember working for a firm where the head of development would caution developers who failed to adhere to correct syntax and development standards. His view, shared by many senior members, was that if you tried to be a "clever dick" and write code that was unnecessarily difficult to read then you were not suitable to work as a developer, who has a responsibility to develop code that is of a level of complexity proportional to the complexity of the problem, and which can be understood in a reasonable period of time by a person of similar ability.
    Last edited by r123456; 11-11-07 at 00:25.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I wouldn't call the implicit join syntax "outdated" (it is in the ISO SQL standard, after all), but I fully agree with you on the coding standards.

    Usually, it takes longer to think about how to resolve a problem than to write it down/type the code in. Thus, everyone using short and cryptic variable names, for example, is just such a "clever dick" for me.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •