Subquery

  • Data Rat

    SSC Eights!

    Points: 896

    Hi Friends,

    I was wondering if someone could give me some help with reading this I guess you would call subquery. Thanks !!!

     

    Begin

    Select COUNT(*) from ouhsc_Common.FileHeader

    where AuditUserId in (

    SELECT distinct(o.UserId) from ouhsc_Security.AccountUsers o

    where o.UnitNumber + o.AccountNumber in

    (

    SELECT au.UnitNumber + au.AccountNumber

    FROM ouhsc_Security.AccountUsers au

    LEFT JOIN dbo.aspnet_Users us

    ON us.UserId=au.UserId

    WHERE us.UserId = @userid

    )

    and ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) in (1,6,7,2)

    )

    and ouhsc_Common.FileHeader.Quarter = @Quarter

    and ouhsc_Common.FileHeader.Year = @Year

    and ouhsc_Common.FileHeader.FileStatusCode not in (300,600,900,1100,1300,1400)

    End

  • Phil Parkin

    SSC Guru

    Points: 243840

    It's a subquery within a subquery (a subsubquery??) – it's much easier to read with some formatting applied:

    BEGIN
    SELECT COUNT(*)
    FROM ouhsc_Common.FileHeader
    WHERE AuditUserId IN
    (
    SELECT DISTINCT
    (o.UserId)
    FROM ouhsc_Security.AccountUsers o
    WHERE o.UnitNumber + o.AccountNumber IN
    (
    SELECT au.UnitNumber + au.AccountNumber
    FROM ouhsc_Security.AccountUsers au
    LEFT JOIN dbo.aspnet_Users us
    ON us.UserId = au.UserId
    WHERE us.UserId = @userid
    )
    AND ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) IN (1, 6, 7, 2)
    )
    AND ouhsc_Common.FileHeader.Quarter = @Quarter
    AND ouhsc_Common.FileHeader.Year = @Year
    AND ouhsc_Common.FileHeader.FileStatusCode NOT IN (300, 600, 900, 1100, 1300, 1400);
    END;

    I suggest you start with the inner subquery and work outwards.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jonathan AC Roberts

    SSCoach

    Points: 16990

    The query smells pretty bad.

    1. It has a LEFT JOIN that is really an INNER JOIN
    2. It is using IN on the concatenation of two columns, this makes that part of the query unsargable.
    3. There is no need to use DISTINCT after IN operator, so it should be removed.

    I would rewrite the query to use EXISTS instead of IN, make the LEFT JOIN an INNER  JOIN and remove the DISTINCT:

    BEGIN
    SELECT COUNT(*) [Count]
    FROM ouhsc_Common.FileHeader
    WHERE EXISTS(SELECT *
    FROM ouhsc_Security.AccountUsers o
    WHERE EXISTS(SELECT *
    FROM ouhsc_Security.AccountUsers au
    INNER JOIN dbo.aspnet_Users us
    ON us.UserId = au.UserId
    WHERE au.UnitNumber = o.UnitNumber
    AND au.AccountNumber = o.AccountNumber
    AND au.UserId = @userid
    )
    AND ouhsc_Security.GetRoleIdFromRoleUid(o.RoleId) IN (1, 6, 7, 2)
    AND o.UserId = ouhsc_Common.FileHeader.AuditUserId
    )
    AND ouhsc_Common.FileHeader.Quarter = @Quarter
    AND ouhsc_Common.FileHeader.Year = @Year
    AND ouhsc_Common.FileHeader.FileStatusCode NOT IN (300, 600, 900, 1100, 1300, 1400);
    END;
  • Data Rat

    SSC Eights!

    Points: 896

    Thanks for your comment. But why would you use an inner join instead of a left join?

  • Jonathan AC Roberts

    SSCoach

    Points: 16990

    Data Rat wrote:

    Thanks for your comment. But why would you use an inner join instead of a left join?

    WHERE us.UserId = @userid
  • Phil Parkin

    SSC Guru

    Points: 243840

    Jonathan AC Roberts wrote:

    Data Rat wrote:

    Thanks for your comment. But why would you use an inner join instead of a left join?

    WHERE us.UserId = @userid

    Because you've effectively turned the LEFT JOIN into an INNER JOIN by adding

    WHERE us.UserId = @userid

    To make the LEFT JOIN meaningful would require something like

    WHERE us.UserId = @userid OR us.UserId IS NULL

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Data Rat

    SSC Eights!

    Points: 896

    That's Excellent. Thanks !!!!

     

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88170

    Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    Data Rat wrote:

    Thanks for your comment. But why would you use an inner join instead of a left join?

    WHERE us.UserId = @userid

    Because you've effectively turned the LEFT JOIN into an INNER JOIN by adding

    WHERE us.UserId = @userid

    To make the LEFT JOIN meaningful would require something like

    WHERE us.UserId = @userid OR us.UserId IS NULL

    Additional comment - when SQL Server sees the outer join it is going to look at how it builds its execution plan differently.  You could end up with a merge join where a hash match would be a better option - but SQL Server cannot use the hash match because it needs to evaluate all rows from the preserved table - join the rows from the outer table - then apply the filter to satisfy the outer join.

    Although the where clause 'effectively' converts the join to an inner join - the resulting execution plan could be a lot different depending on indexes and tables involved.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply