• alex.sqldba - Friday, June 22, 2018 4:57 PM

    Morning Guys,

    The following query is complaining about having no join predicate on a nested loop

    EDIT: The Operator Cost is 0% which perhaps changes where my focus should be?


      SELECT DISTINCT 
       Wizprog 
       ,rar.RequestID  
       ,rar.Processed 
      FROM dbo.PROP_X_CAND_WP xcand  
      JOIN dbo.PROP_PERSON_GEN pg  
       ON pg.REFERENCE = xcand.CANDIDATE 
      JOIN ENTITY_TABLE et 
       ON et.ENTITY_ID = xcand.WIZPROG 
        AND et.STATUS ='Y' 
      LEFT JOIN [A00].[ReferralAnonymousRequest] rar 
       ON rar.ContractREFERENCE = xcand.WIZPROG 
        AND (rar.Processed IS NULL OR rar.Processed = 1) 
        AND AnonymiseIA = 1 
      WHERE pg.REFERENCE = 23423

    My understanding of such a warning was that you inadvertently cause a cartesian product to be returned when using the older star-equals type joins from SQL-89. By which no relationship is defined. However, I don't think I have that, looking at the query posted above. Or am I missing something? Or is my understanding of the warning just wrong?

    Cheers
    Alex

    It may be nothing to worry about, sometimes SQL Server actually can resolve the join as a filter on the source tables as described in this blog post:
    https://www.brentozar.com/archive/2018/03/certainly-join-predicate/
    So check what the predicate is on the Seek in your execution plan.