Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Unanswered: Using JOINS or just the WHERE clause

    This is somthing thats bugging me:

    If you need to run an sql statement where you join 3-4-5 ... (well, alot) of tables. Whats then the best form. Using joins like:
    afwerking INNER JOIN (boorden INNER JOIN (diktes INNER JOIN (Hoofdcategorie INNER JOIN (Soorten INNER JOIN (subcategorie INNER JOIN werkstukken ON subcategorie.nummer = werkstukken.subcategorie) ON Soorten.nummer = werkstukken.soort) ON Hoofdcategorie.nummer = werkstukken.hoofdcategorie) ON diktes.number = werkstukken.dikte) ON boorden.nummer = werkstukken.boordmodel) ON afwerking.nummer = werkstukken.afwerkingopp

    or the where clause
    SELECT DISTINCT p.ProductID, p.Image, p.Price
    FROM products AS p,
    category_links AS c_l,
    categories AS c,
    brands AS b,
    size_links
    AS s_l,
    sizes AS s,
    colour_links AS co_l,
    colours AS co
    WHERE c_l.ProductID = p.ProductID
    AND c.CategoryID = c_l.CategoryID
    AND p.BrandID = b.BrandID
    AND s_l.ProductID = p.ProductID
    AND s.SizeID = s_l.SizeID
    AND co_l.ProductID = p.ProductID
    AND co.ColourID = co_l.ColourID


    Anyone knows which form has best performances or knows some links to articles or tutorials that deal with the performance of these two forms?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Using JOINS or just the WHERE clause

    It's not about performance, it is really just a different style. The JOIN syntax is more modern, and more clearly shows readers how you are joining the tables. It probably also helps reduce the incidence of mistakes when writing queries, e.g. missing join conditions. But since both styles express the same join conditions, there will be zero impact on performance.

  3. #3
    Join Date
    Apr 2003
    Posts
    3
    Thanks for the info.

    Does this also goes when you use outer joins?

    Someone stated that when he used outer joins, the responsetimes increased substantially. (It's not really a common query, so it's more in general that i was wondering if there are indeed such substatial performance differences)
    My geuss is that by combining different outer and inner joins, the RDBM will create more and bigger views before building the recordset, then in the were clause form.
    But i couldn't find any info on it.

    Below are the querys he ran + responsetime.
    code:--------------------------------------------------------------------------------
    SELECT DISTINCT p.ProductID, p.Image, p.Price
    FROM products AS p
    RIGHT JOIN category_links AS c_l
    ON c_l.ProductID = p.ProductID
    INNER JOIN categories AS c
    ON c.CategoryID = c_l.CategoryID
    RIGHT JOIN brands AS b
    ON p.BrandID = b.BrandID
    RIGHT JOIN size_links AS s_l
    ON s_l.ProductID = p.ProductID
    INNER JOIN sizes AS s
    ON s.SizeID = s_l.SizeID
    RIGHT JOIN colour_links AS co_l
    ON co_l.ProductID = p.ProductID
    INNER JOIN colours AS co
    ON co.ColourID = co_l.ColourID
    --------------------------------------------------------------------------------


    code:--------------------------------------------------------------------------------
    SELECT DISTINCT p.ProductID, p.Image, p.Price
    FROM products AS p,
    category_links AS c_l,
    categories AS c,
    brands AS b,
    size_links
    AS s_l,
    sizes AS s,
    colour_links AS co_l,
    colours AS co
    WHERE c_l.ProductID = p.ProductID
    AND c.CategoryID = c_l.CategoryID
    AND p.BrandID = b.BrandID
    AND s_l.ProductID = p.ProductID
    AND s.SizeID = s_l.SizeID
    AND co_l.ProductID = p.ProductID
    AND co.ColourID = co_l.ColourID
    --------------------------------------------------------------------------------
    Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    inner and outer joins are not comparable, because they return different results

    outer joins by definition return unmatched rows

    performance will depend in indexes


    rudy

  5. #5
    Join Date
    Apr 2003
    Posts
    3
    Ok. thanks for the info.

Posting Permissions

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