Sql-server – How to lift a nested windowed query from a left outer join

sql serversql-server-2012

So… I came across the following view. Aside for table design (some names and variables changed to protect the innocent), joins etc. I would like to simplify the query by lifting the two nested SELECT DISTINCT PARTITION BY queries, but my SQL capabilities regarding this are sadly (for the time being) lacking.

I was thinking that perhaps I could just grab the tables in the nested queries, join them with the rest of the tables, and do the MAX/PARTITION BY only once in the SELECT, but I'm having hard time to figuring out how to lift such a windowing function from a join in this particular case or in general. The inner queries are also almost the same.

I'm not sure if bringing this query without tables and example data is all right, but any pointers how to solve this, if possible, with this information, would be gratefully received. This looks deceptively simple, perhaps it is. Spending a bit time in search engines bring similarish ponderings from various places, but not quite anything like this, or in a manner I'm able to proceed, let alone comprehend.

SELECT
    TableAAA.Number, 
    TableCCC.AID, 
    REPLACE(TableCCC.BusinessID, '-', '') AS BusinessID,
    ISNULL(accountAddress.StreetAddress, cccAddress.StreetAddress) AS StreetAddress,
    ISNULL(accountAddress.StreetAddress2, cccAddress.StreetAddress2) AS StreetAddress2,
    ISNULL(accountAddress.StreetAddress3 ,cccAddress.StreetAddress3) AS StreetAddress3,
    ISNULL(accountAddress.PostalCode, cccAddress.PostalCode) AS PostalCode,
    ISNULL(accountAddress.PostOffice, cccAddress.PostOffice) AS PostOffice,
    ISNULL(accountAddress.CountryCode, cccAddress.CountryCode) AS CountryCode,
    TableBBB.ReferenceValue, 
    ISNULL(accBillInfo.BillingInfoID, cccBillingInfo.WayOfBillingID) AS WayOfBillingID,
    ISNULL(accBillInfo.EBillingAddress, cccBillingInfo.EBillingAddress) AS EBillingAddress,
    ISNULL(accBillInfo.OVT, cccBillingInfo.OVT) AS OVT,
    CASE WHEN ac.TypeC = 1 THEN 'K' ELSE 'E' END AS TypeCC, 
    ac.ProductName,
    ISNULL(ac.C, 0) AS Actives
FROM    
    TableAAA
    INNER JOIN TableCCC ON TableAAA.Id = TableCCC.Id
    LEFT JOIN [dbo].[Ac] AS ac ON ac.AccountID = TableAAA.AccountID
    LEFT JOIN TableBBB ON TableAAA.AccountID = TableBBB.AccountID  
    LEFT JOIN dbo.BillingInfo AS accBillInfo ON TableAAA.BillingInfoID = accBillInfo.BillingInfoID      
    LEFT JOIN dbo.BillingInfo_Link_Address AS abla ON abla.BillingInfoID = accBillInfo.BillingInfoID
        AND abla.Address_TypeID = 2
        AND abla.StartTime <= SYSDATETIMEOFFSET()
        AND ISNULL(abla.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
    LEFT JOIN dbo.Address AS accountAddress ON abla.AddressID = accountAddress.AddressID
    -- Company's Billing address information.
    LEFT JOIN dbo.BillingInfo AS cccBillingInfo ON TableCCC.BillingInfoID = cccBillingInfo.BillingInfoID
    LEFT JOIN dbo.BillingInfo_Link_Address AS cbla ON cbla.BillingInfoID = cccBillingInfo.BillingInfoID
        AND cbla.Address_TypeID = 2
        AND cbla.StartTime <= SYSDATETIMEOFFSET()
        AND ISNULL(cbla.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
    LEFT JOIN dbo.Address AS cccAddress ON cbla.AddressID = cccAddress.AddressID
    LEFT JOIN
    (
        SELECT DISTINCT Max(CAST(al.ABlock AS tinyint)) OVER (PARTITION BY acc.Number) AS block, acc.Number
        FROM    
            TableAAA_Limitation AS al
            INNER JOIN TableAAA_Link_Limitation AS aLinkLimit ON al.TableAAA_LimitationID = aLinkLimit.TableAAA_LimitationID 
                AND aLinkLimit.StartTime <= SYSDATETIMEOFFSET() 
                AND ISNULL(aLinkLimit.EndTime, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET()
            INNER JOIN TableAAA AS acc ON acc.TableAAAID = aLinkLimit.TableAAAID 

    ) AS ablock ON ablock.Number = TableAAA.Number 
    LEFT JOIN
    (
        SELECT DISTINCT Max(CAST(cl.ABlock AS tinyint)) OVER (PARTITION BY ccc.AID) AS block, ccc.AID
        FROM    
            dbo.Company_Limitation AS cl
            INNER JOIN dbo.TableCCC_Link_Limitation AS cLinkLimit ON cl.CCC_LimitationID = cLinkLimit.TableCCC_LimitationID 
                AND cLinkLimit.StartDate <= SYSDATETIMEOFFSET()                     
                AND (ISNULL(cLinkLimit.EndDate, DATEADD(month, 1, SYSDATETIMEOFFSET())) >= SYSDATETIMEOFFSET())
            INNER JOIN TableCCC AS ccc ON ccc.Id = cLinkLimit.Id 
    ) AS cccBlock ON cccBlock.AID = TableCCC.AID
WHERE   
    (ISNULL(ablock.block, 0) < 1) 
    AND (ISNULL(cccBlock.block, 0) < 1)

Best Answer

SELECT DISTINCT MAX(x) OVER (PARTITION BY y), y, when you're not returning any other columns is the same as SELECT MAX(x), y ... GROUP BY y. I'd suggest you start by doing that, and you may find your abilities are well capable of doing further refactoring.

Related Solutions

Sql-server – difference of left join and left outer join

There is no difference, they are absolutely 100% identical in function - the OUTER keyword is optional in the syntax. Personally I prefer LEFT OUTER JOIN as it's a bit more explicit.

You can see more details here:

MSDN : Using Outer Joins

and here:

MSDN : FROM (Transact-SQL)

The relevant bit in the latter document:

LEFT [ OUTER ]

Specifies that all rows from the left table not meeting the join condition are included in the result set, and output columns from the other table are set to NULL in addition to all rows returned by the inner join

The same description applies whether you use the OUTER keyword or not.

Sql-server – Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

If you look at the 2 execution plans, is there an easy answer to which is better? I purposefully did NOT create indexes so it's easier to see what's happening.

The second plan has a lower estimated cost, so in that limited sense it is 'better'.

The data sets are so small that the optimizer did not spend much time looking at alternatives. The first form of the query happens to find a plan using hash join and a table spool early on. The estimated cost of that plan is so low that the optimizer does not bother looking for anything better.

The second form of the query happens to find a plan using only nested loops outer joins early in the search process, and again the optimizer decides that plan is good enough. It so happens that this plan is estimated to be cheaper.

That said (as mentioned in the question comments) the two queries are not semantically identical. This may not be important to you if you can guarantee that the results will always be the same for all possible future states of your database, but the optimizer cannot make that assumption. It only ever produces plans that are guaranteed to produce the same results specified by the SQL, in all circumstances.

I have realized that the nested syntax also modifies the behaviour of the query.

The 'nested syntax' is just one aspect of the whole ANSI join syntax specification. To enable a full logical specification for more complex join patterns, the specification allows (optional) parentheses, and FROM clause subqueries.

The query can be written using the same ANSI syntax using parentheses:

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Autos AS A
LEFT JOIN
(
    dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) ON M.ModelID = A.ModelID;

This form clearly shows that the logical requirement is to left join from Autos to the result of inner joining Manufacturers to Models. Omitting the optional parentheses gives the form you call 'nested':

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Autos AS A
LEFT JOIN dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
    ON M.ModelID = A.ModelID;

This is not a different syntax - it is just omitting optional parentheses and reformatting a bit.

As Martin mentioned, it is also possible in this case to express the logical requirement using inner joins followed by a right outer join:

SELECT
    A.*,
    M.*,
    N.* 
FROM dbo.Manufacturers AS N
JOIN dbo.Models AS M
    ON M.ManufacturerID = N.ManufacturerID
RIGHT JOIN dbo.Autos AS A
    ON A.ModelID = M.ModelID;

All three query forms above use the same ANSI join syntax. All three also happen to produce the same physical execution plan with the data set provided:

Common execution plan

As I mentioned in my answer to your previous question, queries that express exactly the same logical requirement will not always produce the same execution plan. Which logical query form you prefer to use is largely a question of style. There is no correlation between one particular style and 'better' query plans in general. I would generally advise against rewriting a query to get a particular plan if the new query is not genuinely logically identical to the original.

The SQL standard also allows FROM clause subqueries, so yet another way to write the same query specification is:

SELECT * 
FROM dbo.Autos AS A
LEFT JOIN
(
    SELECT
        N.ManufacturerID,
        ManufacturerName = N.Name,
        M.ModelID,
        ModelName = M.Name
    FROM dbo.Manufacturers AS N
    JOIN dbo.Models AS M
        ON M.ManufacturerID = N.ManufacturerID
) AS R1
    ON R1.ModelID = A.ModelID;

Using the traditional syntax, we have to change the join to `Manufacturers to an outer join, like so... but this changes the query plan.

This probably changes the meaning of the query, in which case it is technically not a valid alternative (but see ypercube's comment on your question).

The (optional) parentheses in the ANSI join syntax are there precisely for more complex join requirements like this, so you should not be afraid to use them where necessary.

Best Answer

Related Solutions

Sql-server – difference of left join and left outer join

Sql-server – Syntax of INNER JOIN nested inside OUTER JOIN vs. query results

Related Question