SQL help?

Discussion in 'OT Technology' started by yoloo, Dec 31, 2002.

  1. yoloo

    yoloo hi

    Joined:
    Nov 18, 2001
    Messages:
    62
    Likes Received:
    0
    Location:
    Houston, TX
    can someone tell me the difference between these two SQL commands? they give the same result set, but which way would you recommend over the other and why?


    1
    SELECT tblProduct.SKU, tblProduct.Description, tblProduct.UnitPrice, tblPurchaseDetail.Quantity
    FROM tblProduct INNER JOIN tblPurchaseDetail ON tblProduct.SKU = tblPurchaseDetail.SKU
    ORDER BY tblProduct.SKU;

    2
    SELECT tblProduct.SKU, tblProduct.Description, tblProduct.UnitPrice, tblPurchaseDetail.Quantity
    FROM tblProduct, tblPurchaseDetail
    WHERE tblProduct.SKU = tblPurchaseDetail.SKU
    ORDER BY tblProduct.SKU;
     
  2. Dommi

    Dommi Guest

    the only differences that I see are the INNER JOIN and the ON commands...
    inner join (atleast in mysql) is the same as comma seperation...

    ON according to the mysql manual doesnt even exist...
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    i'm guessing you're either using Access or SQL server right?

    Like Dommi said, the only difference is how the join is executed. The 'ON' statement is just a shortcut or alias for the WHERE clause where you match the two fields for the join.
     
  4. Dommi

    Dommi Guest

    didnt know that
    :bowdown:
     
  5. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    I didn't know that either.. So does one have any performance gains over another? I wouldn't think so if it's just a shortcut/alias to a where clause.
     
  6. yoloo

    yoloo hi

    Joined:
    Nov 18, 2001
    Messages:
    62
    Likes Received:
    0
    Location:
    Houston, TX
    i'm using access;

    i duno...they both give me the same result set;

    for the method using INNER JOIN: in Access' query design window (or query by example window), the two tables are joined with a relationship.

    using the second method (without the INNER JOIN and the ON): the tables are not connected with a relationship in the query design window.

    i'm trying to find out what jbajin wanted to know also: So does one have any performance gains over another
     
  7. importspeed

    importspeed New Member

    Joined:
    Jan 3, 2001
    Messages:
    36
    Likes Received:
    0
    Location:
    Massachusetts
    I am not postitive, as my experience is in Oracle, but I would think the equi- join would work the best. The inner join is very similar to it, but I would think it would be better from the optimizers standpoint to use an equi join. Most RDMS softwares tend to like = as upposed to using and inner or outer join.
     
  8. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    don't rely on the Access visual aids, or any visual aids for that matter. They can be deceiving, especially when you write you queries by hand. I'm not sure that one method really has any performance enhancements over the other. They both really end up doing the same thing in the end.
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I could be wrong, but you may find that it returns the same data, but the type of recordset it returns maybe slightly different (dynaset, snapshot...). I remember running into this problem before and I had to do it one way and not the other but it was a long time ago now. I know the join allows for recursive changes, where as the 2nd way treats the tables separately.
     
  10. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    hmmm, recursive changes? With an update statement, or through the recordset? I know this code works either way
    Code:
    UPDATE Products
    SET whatever = something
    FROM Products JOIN Product_Cat ON Products.CatCode = Product_Cat.Catcode
    Where Products.ProductID = 'xxxx'
    
    UPDATE Products
    SET whatever = something
    FROM Products, Product_Cat 
    Where Products.ProductID = 'xxxx'
    AND Products.CatCode = Product_Cat.Catcode
    
    

    I really don't think the join type would have an effect on the way it is treated in ADO, DAO, OLEdb or whatever you're using. It would be dependant on your cursor type. I know that you know Access really well, so maybe Access treats it differently?
     

Share This Page