0

I have a query that uses a join hint to force a HASH join between a table and a view. I noticed that there was missing data in the query when I use the query hint to force the HASH join. When I run the two queries there 65 NULL rows (out of about 40,000) in the HASH join query that have data in the query without a join hint. I did not think that using a query hint would impact the rows that were returned. I have not been able to find anything in common among the rows that are not returned by the query using the HASH join.

This is the query that I am using:

SELECT denial_letter.case_id, denial_letter.mailed_date FROM [hpXr_Stage].[psa].[PSA_CAE_OLTP_ORG_CASE] org_case
         LEFT HASH JOIN hpXr_Stage.stg.SHS_V_CAE_UM_DENIAL_LETTER denial_letter ON denial_letter.case_id = org_case.id
            AND denial_letter.mailed_date_order = 1
            AND denial_letter.create_date_order = 1


SELECT denial_letter.case_id, denial_letter.mailed_date FROM [hpXr_Stage].[psa].[PSA_CAE_OLTP_ORG_CASE] org_case
         JOIN hpXr_Stage.stg.SHS_V_CAE_UM_DENIAL_LETTER denial_letter ON denial_letter.case_id = org_case.id
            AND denial_letter.mailed_date_order = 1
            AND denial_letter.create_date_order = 1
Dave
  • 61
  • 6
  • I never found the reason that this was happening but it must have had something to do with using a join hint to connect to the view. I removed the views and used temp tables to hold the data instead and that resolved the issue. – Dave Apr 06 '22 at 13:17
  • 1
    The queries are not equivalent. You are using a LEFT OUTER JOIN in the first query and an INNER JOIN for the second. – Øystein Grøvlen May 10 '22 at 11:58
  • That actually does not matter in this case. I used the left outer join to demonstrate that the row counts could be the same, but that for the hash join the data was missing, or NULL, as I said in the post. If I use an inner join for both joins then the rows are simply missing. If I use a left join for both then the row count is the same, but there are nulls in the query with the join hint. – Dave May 11 '22 at 12:16

0 Answers0