Why don't these two queries return the same data?

Question

Why don't these two queries return the same data?

SSCertifiable

Points: 5825

December 12, 2023 at 2:58 pm

I have a table-value function that returns data for a report. However, it's not returning the correct data, so I've reworked it and it's now returning what I'd expect ... the thing is, to my obviously unseeing eyes the two queries should be functionally identical. Please can someone help?

This query doesn't work:

SELECT
   Grades.[Name] AS Grade,
   CASE WHEN Grades.[Rank] IS NULL THEN 0 ELSE Grades.[Rank] END AS GradeRank,
   Specialties.[Name] AS Specialty
FROM 
   [Roster].[PublishedShifts] AS PShift 
   LEFT JOIN (
   SELECT TOP 1 
      Specialties.[Name],
      StaffSpecialties.StaffId
   FROM 
      [Staff].[StaffSpecialties] AS StaffSpecialties INNER JOIN [Organisation].[Specialties] AS 
      Specialties ON StaffSpecialties.SpecialtyId = Specialties.Id 
   ORDER BY
   StaffSpecialties.StartDate DESC) AS Specialties ON PShift.StaffId = Specialties.StaffId
   LEFT JOIN (
   SELECT TOP 1 
      Grades.[Name],
      StaffGrades.StaffId,
      Grades.[Rank]
   FROM 
      [Staff].[StaffGrades] AS StaffGrades INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId 
      = Grades.Id 
   ORDER BY
      StaffGrades.StartDate DESC) AS Grades ON PShift.StaffId = Grades.StaffId

This query works:

SELECT
   (SELECT TOP 1 
      Grades.[Name] AS [GradeName]
   FROM 
      [Staff].[StaffGrades] AS StaffGrades
      INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId = Grades.Id 
   WHERE
      (PShift.StaffId = StaffGrades.StaffId)
   ORDER BY
      StaffGrades.StartDate DESC) AS Grade,
   (SELECT TOP 1 
      ISNULL(Grades.[Rank], 0) AS [Rank]
   FROM 
      [Staff].[StaffGrades] AS StaffGrades
      INNER JOIN [Staff].[Grades] AS Grades ON StaffGrades.GradeId = Grades.Id 
   WHERE
      (PShift.StaffId = StaffGrades.StaffId)
   ORDER BY
      StaffGrades.StartDate DESC) AS [Rank],
   (SELECT TOP 1 
      Specialties.[Name]
   FROM 
      [Staff].[StaffSpecialties] AS StaffSpecialties INNER JOIN [Organisation].[Specialties] AS Specialties ON StaffSpecialties.SpecialtyId = Specialties.Id 
   WHERE
      (PShift.StaffId = StaffSpecialties.StaffId)
   ORDER BY
      StaffSpecialties.StartDate DESC) AS Specialty
FROM 
   [Roster].[PublishedShifts] AS PShift

Thank you!

This topic was modified 9 months, 1 week ago by edwardwill. Reason: Fixed missing formatting

Viewing 4 posts - 1 through 3 (of 3 total)

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

Phil Parkin SSC Guru Points: 246873 More actions · Answer 1

In the one that doesn't work, I suspect it's because the TOP (1) is being applied before the join on StaffId.

If you were to push the StaffId into the subqueries to make them correlated, it might make them work as expected.

This reply was modified 9 months, 1 week ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18313 More actions · Answer 2

You are thinking LEFT JOIN works like OUTER APPLY (it doesn't). You can rewrite the first query using OUTER APPLY instead of LEFT JOIN:

SELECT Grades.[Name] AS Grade,
       CASE WHEN Grades.[Rank] IS NULL THEN 0 ELSE Grades.[Rank] END AS GradeRank,
       Specialties.[Name] AS Specialty
  FROM [Roster].[PublishedShifts] AS PShift 
 OUTER APPLY (SELECT TOP 1 
                     Specialties.[Name],
                     StaffSpecialties.StaffId
                FROM [Staff].[StaffSpecialties] AS StaffSpecialties 
               INNER JOIN [Organisation].[Specialties] AS Specialties 
                       ON StaffSpecialties.SpecialtyId = Specialties.Id 
               WHERE StaffSpecialties.StaffId = PShift.StaffId 
               ORDER BY StaffSpecialties.StartDate DESC) AS Specialties 
 OUTER APPLY (SELECT TOP 1 
                     Grades.[Name],
                     StaffGrades.StaffId,
                     Grades.[Rank]
                FROM [Staff].[StaffGrades] AS StaffGrades 
               INNER JOIN [Staff].[Grades] AS Grades 
                       ON StaffGrades.GradeId = Grades.Id 
               WHERE StaffGrades.StaffId = PShift.StaffId 
               ORDER BY StaffGrades.StartDate DESC) AS Grades 
;

edwardwill SSCertifiable Points: 5825 More actions · Answer 3

Thanks to both of you. I have amended the query using OUTER APPLY (which I've only rarely used before) and that works fine.