locked
using EXISTS instead of JOIN? RRS feed

  • Question

  • Current code:

    FROM INVENTVALUETRANSVIEW T1 CROSS JOIN INVENTDIM T2 
    
    WHERE (T1.INVENTDIMID=T2.INVENTDIMID AND (T1.DATAAREAID = T2.DATAAREAID) AND (T1.PARTITION = T2.PARTITION)) AND EXISTS 
    (SELECT 'x' FROM INVENTTABLE T3 WHERE (T1.ITEMID=T3.ITEMID AND (T1.DATAAREAID = T3.DATAAREAID) AND (T1.PARTITION = T3.PARTITION)) AND (EXISTS (SELECT 'x' FROM ECORESPRODUCT T4 WHERE ((T4.PRODUCTTYPE=1) AND (T3.PRODUCT=T4.RECID AND (T3.PARTITION = T4.PARTITION))))) 
    AND (EXISTS (SELECT 'x' FROM INVENTMODELGROUPITEM T5 WHERE ((T5.ITEMID=T3.ITEMID AND (T5.PARTITION = T3.PARTITION)) 
    AND (T5.ITEMDATAAREAID=T3.DATAAREAID AND (T5.PARTITION = T3.PARTITION)))
     AND EXISTS (SELECT 'x' FROM INVENTMODELGROUP T6 WHERE ((T6.STOCKEDPRODUCT=1) AND ((T5.MODELGROUPID=T6.MODELGROUPID AND (T5.PARTITION = T6.PARTITION)) AND (T5.MODELGROUPDATAAREAID=T6.DATAAREAID AND (T5.PARTITION = T6.PARTITION))))))))
    GO
    

    i wonder, why not doing lookups via JOIN for T3,T4,T5??

    thanks for your comments,

    Monday, June 18, 2018 10:18 AM

Answers

  • First of all: Don't use any magic in the SELECT list for EXISTS. This is one of the use-cases, where the asterisk is just perfect.

    And: The CROSS JOIN is unnecessary. It is in fact an INNER JOIN.

    Then: Get rid of all unnecessary parentheses. They clutter the statement beyond readability.

    You seem also to have redundant predicates in your WHERE clauses.

    So you can shorten it to:

    SELECT *
    FROM   INVENTVALUETRANSVIEW T1
           INNER JOIN INVENTDIM T2 ON T1.INVENTDIMID = T2.INVENTDIMID
                                      AND T1.DATAAREAID = T2.DATAAREAID
                                      AND T1.PARTITION = T2.PARTITION
    WHERE  EXISTS (   SELECT *
                      FROM   INVENTTABLE T3
                      WHERE  T1.ITEMID = T3.ITEMID
                             AND T1.DATAAREAID = T3.DATAAREAID
                             AND T1.PARTITION = T3.PARTITION
                             AND EXISTS (   SELECT *
                                            FROM   ECORESPRODUCT T4
                                            WHERE  T4.PRODUCTTYPE = 1
                                                   AND T3.PRODUCT = T4.RECID
                                                   AND T3.PARTITION = T4.PARTITION )
                             AND EXISTS (   SELECT *
                                            FROM   INVENTMODELGROUPITEM T5
                                            WHERE  T5.ITEMID = T3.ITEMID
                                                   AND T5.PARTITION = T3.PARTITION
                                                   AND T5.ITEMDATAAREAID = T3.DATAAREAID
                                                   AND EXISTS (   SELECT *
                                                                  FROM   INVENTMODELGROUP T6
                                                                  WHERE  T6.STOCKEDPRODUCT = 1
                                                                         AND T5.MODELGROUPID = T6.MODELGROUPID
                                                                         AND T5.PARTITION = T6.PARTITION
                                                                         AND T5.MODELGROUPDATAAREAID = T6.DATAAREAID )));
    
    
    

    And for your question: Cause having matches in the JOINs would change the result set. Thus you can use JOINs only in the first EXISTS() as:

    SELECT *
    FROM   INVENTVALUETRANSVIEW T1
           INNER JOIN INVENTDIM T2 ON T1.INVENTDIMID = T2.INVENTDIMID
                                      AND T1.DATAAREAID = T2.DATAAREAID
                                      AND T1.PARTITION = T2.PARTITION
    WHERE  EXISTS (   SELECT *
                      FROM   INVENTTABLE T3
                             INNER JOIN ECORESPRODUCT T4 ON T4.PRODUCTTYPE = 1
                                                            AND T3.PRODUCT = T4.RECID
                                                            AND T3.PARTITION = T4.PARTITION
                             INNER JOIN INVENTMODELGROUPITEM T5 ON T5.ITEMID = T3.ITEMID
                                                                   AND T5.PARTITION = T3.PARTITION
                                                                   AND T5.ITEMDATAAREAID = T3.DATAAREAID
                             INNER JOIN INVENTMODELGROUP T6 ON T6.STOCKEDPRODUCT = 1
                                                               AND T5.MODELGROUPID = T6.MODELGROUPID
                                                               AND T5.PARTITION = T6.PARTITION
                                                               AND T5.MODELGROUPDATAAREAID = T6.DATAAREAID
                      WHERE  T1.ITEMID = T3.ITEMID
                             AND T1.DATAAREAID = T3.DATAAREAID
                             AND T1.PARTITION = T3.PARTITION );
    
    

    Caveat: Check the actual execution plans of all approaches.




    Monday, June 18, 2018 10:55 AM

All replies

  • the way you've written it its nesting one EXISTS check inside the other

    so effectively what you've is a single EXISTS clause in your query

    The moment you nest it, the evaluation of conditions are different

    i.e where condi where cond2 where cond3 is the way it eveluates which is diff from

    where cond1

    and cond2

    and cond3

    I think may be what you're looking at is something like this

    FROM INVENTVALUETRANSVIEW T1 CROSS JOIN INVENTDIM T2 
    
    WHERE T1.INVENTDIMID=T2.INVENTDIMID 
    AND T1.DATAAREAID = T2.DATAAREAID 
    AND T1.PARTITION = T2.PARTITION
    AND EXISTS 
    (SELECT 'x' 
    FROM INVENTTABLE T3 
    WHERE 
    T1.ITEMID=T3.ITEMID 
    AND T1.DATAAREAID = T3.DATAAREAID 
    AND T1.PARTITION = T3.PARTITION
    )
    AND EXISTS 
    (SELECT 'x' FROM ECORESPRODUCT T4 WHERE (T4.PRODUCTTYPE=1 AND T3.PRODUCT=T4.RECID AND T3.PARTITION = T4.PARTITION))
    AND 
    EXISTS (
    SELECT 'x' FROM INVENTMODELGROUPITEM T5 WHERE (T5.ITEMID=T3.ITEMID AND T5.PARTITION = T3.PARTITION 
    AND T5.ITEMDATAAREAID=T3.DATAAREAID AND T5.PARTITION = T3.PARTITION)
    )
    AND 
     EXISTS (
     SELECT 'x' FROM INVENTMODELGROUP T6 
     WHERE (T6.STOCKEDPRODUCT=1
     AND T5.MODELGROUPID=T6.MODELGROUPID
      AND T5.PARTITION = T6.PARTITION AND T5.MODELGROUPDATAAREAID=T6.DATAAREAID AND T5.PARTITION = T6.PARTITION))
    GO

    i removed some braces as I don't think it makes any difference as those are all AND conditions. It will reduce some confusion in understanding the code as well.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, June 18, 2018 10:27 AM
  • so, we don't be able to use any substitution for semi join here?

    There will not no differences when using IN clause instead of EXISTS?

    Monday, June 18, 2018 10:37 AM
  • so, we don't be able to use any substitution for semi join here?

    There will not no differences when using IN clause instead of EXISTS?

    sorry didnt understand

    Can you explain what exactly you're trying to do with the query above in words?

    I can only see the incomplete query part you posted so have no idea on what your original intention was

    I was just explaining it based on the way you've written it 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, June 18, 2018 10:46 AM
  • First of all: Don't use any magic in the SELECT list for EXISTS. This is one of the use-cases, where the asterisk is just perfect.

    And: The CROSS JOIN is unnecessary. It is in fact an INNER JOIN.

    Then: Get rid of all unnecessary parentheses. They clutter the statement beyond readability.

    You seem also to have redundant predicates in your WHERE clauses.

    So you can shorten it to:

    SELECT *
    FROM   INVENTVALUETRANSVIEW T1
           INNER JOIN INVENTDIM T2 ON T1.INVENTDIMID = T2.INVENTDIMID
                                      AND T1.DATAAREAID = T2.DATAAREAID
                                      AND T1.PARTITION = T2.PARTITION
    WHERE  EXISTS (   SELECT *
                      FROM   INVENTTABLE T3
                      WHERE  T1.ITEMID = T3.ITEMID
                             AND T1.DATAAREAID = T3.DATAAREAID
                             AND T1.PARTITION = T3.PARTITION
                             AND EXISTS (   SELECT *
                                            FROM   ECORESPRODUCT T4
                                            WHERE  T4.PRODUCTTYPE = 1
                                                   AND T3.PRODUCT = T4.RECID
                                                   AND T3.PARTITION = T4.PARTITION )
                             AND EXISTS (   SELECT *
                                            FROM   INVENTMODELGROUPITEM T5
                                            WHERE  T5.ITEMID = T3.ITEMID
                                                   AND T5.PARTITION = T3.PARTITION
                                                   AND T5.ITEMDATAAREAID = T3.DATAAREAID
                                                   AND EXISTS (   SELECT *
                                                                  FROM   INVENTMODELGROUP T6
                                                                  WHERE  T6.STOCKEDPRODUCT = 1
                                                                         AND T5.MODELGROUPID = T6.MODELGROUPID
                                                                         AND T5.PARTITION = T6.PARTITION
                                                                         AND T5.MODELGROUPDATAAREAID = T6.DATAAREAID )));
    
    
    

    And for your question: Cause having matches in the JOINs would change the result set. Thus you can use JOINs only in the first EXISTS() as:

    SELECT *
    FROM   INVENTVALUETRANSVIEW T1
           INNER JOIN INVENTDIM T2 ON T1.INVENTDIMID = T2.INVENTDIMID
                                      AND T1.DATAAREAID = T2.DATAAREAID
                                      AND T1.PARTITION = T2.PARTITION
    WHERE  EXISTS (   SELECT *
                      FROM   INVENTTABLE T3
                             INNER JOIN ECORESPRODUCT T4 ON T4.PRODUCTTYPE = 1
                                                            AND T3.PRODUCT = T4.RECID
                                                            AND T3.PARTITION = T4.PARTITION
                             INNER JOIN INVENTMODELGROUPITEM T5 ON T5.ITEMID = T3.ITEMID
                                                                   AND T5.PARTITION = T3.PARTITION
                                                                   AND T5.ITEMDATAAREAID = T3.DATAAREAID
                             INNER JOIN INVENTMODELGROUP T6 ON T6.STOCKEDPRODUCT = 1
                                                               AND T5.MODELGROUPID = T6.MODELGROUPID
                                                               AND T5.PARTITION = T6.PARTITION
                                                               AND T5.MODELGROUPDATAAREAID = T6.DATAAREAID
                      WHERE  T1.ITEMID = T3.ITEMID
                             AND T1.DATAAREAID = T3.DATAAREAID
                             AND T1.PARTITION = T3.PARTITION );
    
    

    Caveat: Check the actual execution plans of all approaches.




    Monday, June 18, 2018 10:55 AM
  • Thanks Stefan
    Monday, June 18, 2018 12:43 PM
  • Hi Visakh, yes certainly only showed part of the view.

    "Can you explain what exactly you're trying to do with the query above in words?"

    I dunno, it does take 16 minutes. The other guy uses this view in a cube.

    Thanks for your posts!!

    Monday, June 18, 2018 12:44 PM