Left Join vs Inner Join (1 Viewer)

[Guus2005]

If a left join produces the same result as an inner join which is the better choice?
Which one is faster?

 

[Pat Hartman]

The only time they produce the same result is when tbl2 has matches for every row in tbl1

Inner joins are faster but speed isn't what dictates the choice. It is the logic of the join that dictates the choice of join. The type is not arbitrary. Every join type has a specific use despite the fact that they will occasionally return the same results.

 

[ebs17]

Interesting question after 15 years of working with Access.

same result

That depends on the data. If only the same keys occur in both tables, the result is identical.

Inner joins are faster

Why?
I think in both cases the keys of the two tables are compared with each other using CROSS JOIN. With the LEFT JOIN, all keys of the left table are taken over in differentiation, with the INNER JOIN only the matching ones. That makes practically no significant difference.

 

[CJ_London]

I can feel a Colin speedtest coming on

 

[theDBguy]

I can feel a Colin speedtest coming on

I was about to say the same thing. Perhaps use Jet Show Plan?

 

[Pat Hartman]

Regarding speed - I was just repeating a report I read. I'm not set up for a speed test. Colin is if he cares to do one.

 

[ebs17]

For a realistic speed test, the query used should only show the keys in the SELECT part.

If additional fields are used, different content is reloaded due to the different functionality, so with the LEFT JOIN there may be some more. Then you can expect differences.

 

[Pat Hartman]

For a realistic speed test, the query used should only show the keys in the SELECT part.

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?

 

[ebs17]

A measurement should make sense. You should define for yourself whether you mean a comparison between OUTER JOIN and INNER JOIN or a comparison of queries that contain an OUTER JOIN or an INNER JOIN. I was orientated towards the first case, and I have to model it a bit to compare the same. If thick memo fields or other masses of data are loaded on one side, there are already explainable differences.

Of course, you could also model it in such a way that you use two tables in a 1:n relationship with referential integrity set, where there are foreign keys in the secondary table for every primary key in the primary table, so that the result and thus the loading of data match.

The already mentioned different functionality of the JOIN variants in various different practical tasks will lead to differences, but where measurements are then value-free.

 

[isladogs]

I can feel a Colin speedtest coming on

No need as already done long ago!
This formed a small part of the series of tests in my Optimising Queries article:

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...

www.isladogs.co.uk

JET ShowPlan outputs provided in each case
It is indeed true that running inner joins is significantly faster than outer joins (where the output data is identical in each case)

 

[Guus2005]

I am going to change the LEFT JOIN to an INNER JOIN.

Thanks for all the answers and Colin thanks for the speed comparison tests.

 

[isladogs]

You're welcome

 

[ebs17]

Speed Comparison Tests by isladogs​

Measurements are convincing in the result, even if the queries do a lot more than just performing JOINs. Since the execution plans are also different, I am converted in my opinion. Thanks to isladogs for the documents provided.

where the output data is identical in each case

The intermediate result after the JOINs before filtering and grouping is different, but that's not enough to explain the rather large difference.

Optimise Queries
The following variant could be included in a test:

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)

The story behind it: The linking of table values has a higher weight than the filtering of a field. It therefore makes sense to filter first and then link the smaller tables.

 

[isladogs]

@ebs17
Thanks for the suggestion which I've added at the end of the list of tests.
It only took a few minutes to edit the example app

As its obviously a variation on Test J, I've called it Sequenced First vs Group By
I've run some tests and also checked the JET ShowPlan output for it compared to Test J.

Your final sentence gives an indication of what you think will happen ...
However, before I tell you the results, I'd be interested in your views on how it will compare in terms of speed with test J.
Similarly, any thoughts you may have on how the two execution plans differ

 

[ebs17]

I expect the same to better speed. Why: A JOIN is more complex than a filter on a field. Prof. Dr. Felix Naumann (Hasso Plattner Institute, University of Potsdam) put the difference at a flat rate of 3:1. Indices would either have to be used equally for both operations or not.

So you would do the lighter operation of filtering first to get smaller tables, which then does the more involved operation of JOIN over fewer records.
I hope I can give the SQL optimizer a good template that he understands and can implement profitably.

 

[isladogs]

Thanks for the speedy reply which was what I thought you were expecting.
I don;t know anything about the person you quoted so can only go on my own experience & results.

Inevitably the results are similar as its basically the same query
In fact, the query execution plans are identical - remember that Access will always attempt to optimise the query execution whether written as in the original test (J) or your variant (L)

.
So you might expect the speed test results would be identical.
WRONG!

I ran all tests again 10 times ...

The new version is consistently slightly slower by about 0.01s (approx 1.5%)
A small but measurable difference. So why is that?

I have a theory which I'm going to test but in the meantime . . . why do you think it might be slower?

I am attaching the database & the 2 show plan files so you can test for yourself

 

[Pat Hartman]

I am going to change the LEFT JOIN to an INNER JOIN.

What part of join types are not arbitrary do you not understand? You need to use the join type that returns the set of data you are looking for. Taking the Acela to DC just because it is faster doesn't help at all when you really wanted to go to Boston. If you really need an inner join, then by all means change the join but make certain that you understand how changing the join will impact the records returned by the query. As I pointed out earlier, the two joins will only return the same result set if every row in tbl1 has a matching row in tbl2.

 

[isladogs]

The OP clearly stated in post #1 that he was referring to a situation where both types of join had the same result.

 

[Guus2005]

Hi Pat, never seen a hostile response from you. But here we are.

I didn't create the query and yes i know that you should focus on what you want to achieve. I was suprised when the outcome for both joins was the same and i needed an extra arguments to convince the person who wrote the offending query to change the join. I even added a picture with venn-diagrams showing the result of a particular join.

The problem was fixed. Thanks for your input

 

[ebs17]

outcome for both joins was the same

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?

I hope you don't mind if we have hijacked your topic here for a more in-depth discussion.