That depends on the data. If only the same keys occur in both tables, the result is identical.same result
Why?Inner joins are faster
I was about to say the same thing. Perhaps use Jet Show Plan?I can feel a Colin speedtest coming on
I disagree. The query will always select columns from both tables to be realistic. Limiting the test to just key fields is unrealistic since it does not reflect the real world and we are talking about real world scenarios. Who cares about unnatural situations?For a realistic speed test, the query used should only show the keys in the SELECT part.
No need as already done long ago!I can feel a Colin speedtest coming on
The intermediate result after the JOINs before filtering and grouping is different, but that's not enough to explain the rather large difference.where the output data is identical in each case
SELECT
D.PupilID,
FIRST(D.Surname) AS LastName,
FIRST(D.Forename) AS FirstName,
FIRST(D.YearGroup) AS YearGp,
R.Code,
FIRST(C.Description) AS CodeType,
FIRST(R.MeritPts) AS Merits,
FIRST(R.DeMeritPts) AS Demerits,
COUNT(*) AS Incidents
FROM
(
SELECT
PupilID,
Surname,
Forename,
YearGroup
FROM
PupilData
WHERE
DateOfBirth BETWEEN #1/1/2005# AND #12/31/2005#
) AS D
INNER JOIN
(PRCodes AS C
INNER JOIN
(
SELECT
PupilID,
Code,
MeritPts,
DeMeritPts
FROM
PRecords
WHERE
DateOfIncident BETWEEN #1/1/2018# AND #12/30/2018#
) AS R
ON C.Code = R.Code
)
ON D.PupilID = R.PupilID
GROUP BY
D.PupilID,
R.Code
ORDER BY
FIRST(D.Surname),
FIRST(D.Forename)
I am going to change the LEFT JOIN to an INNER JOIN.
This is a special case with a certain data situation. But you shouldn't rely on this. If additional records are added to a table, the output can change very quickly. At some point does the person want to immediately change the query design because of new data?outcome for both joins was the same