SQL Server Performance

Replacing sub query with inner join?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by matt3.5, Sep 3, 2008.

  1. matt3.5 New Member

    Hi, how can I get another inner join in here to improve the performance of this query? I'm querying a training courses database.
    The query below works fine but uses a sub query and an inner join, I read that sub queries are not great for large datasets so would like to use another inner join to get the data from the other table. Any ideas?
    SELECT Product.ProductId, ProductName, Duration, Discounted,
    (SELECT QualificationName FROM dbo.ProductQualification WHERE dbo.ProductQualification.QualificationId = dbo.Product.QualificationId) AS Qualification
    FROM dbo.Product INNER JOIN dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId
    WHERE dbo.ProductCategory_Junction.CategoryId = 2
    Any idea how I could replace the sub query with an inner join? Or is it just not worth it..?
  2. FrankKalis Moderator

    I might be missing something but aer you looking after this?
    SELECT
    Product.ProductId, ProductName, Duration, Discounted,
    dbo.ProductQualification.QualificationName AS Qualification
    FROM
    dbo.Product
    JOIN
    dbo.ProductQualification on dbo.ProductQualification.QualificationId = dbo.Product.QualificationId
    JOIN
    dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId
    WHERE
    dbo.ProductCategory_Junction.CategoryId = 2
  3. matt3.5 New Member

    Frank, YOU ARE THE MAN!
  4. martins New Member

    I would go for a left outer join, unless every single product will have a corresponding entry in the ProductQualification table [:)]
    SELECT Product.ProductId
    , ProductName
    , Duration
    , Discounted
    , ProductQualification.QualificationName AS Qualification
    FROM dbo.Product
    INNER JOIN dbo.ProductCategory_Junction ON Product.ProductId = dbo.ProductCategory_Junction.ProductId
    LEFT OUTER JOIN dbo.ProductQualification ON dbo.ProductQualification.QualificationId = dbo.Product.QualificationId
    WHERE dbo.ProductCategory_Junction.CategoryId = 2

Share This Page