Can I mix LEFT JOINS and INNER JOINS in Access 2010 SQL?

Question

TL;DR: I want to know if it's possible to mix LEFT and INNER joins in Access, as LEFT joins are (as is to be expected) slowing my query down significantly.

Full version: Yesterday, I was having an issue that it turns out was caused by Access 2010 not liking me mixing LEFT and INNER JOINs in a combined query. A little recap here:

I had three queries, which I wished to combine. Each of the constituent queries was constructed using several tables and INNER JOINs.

I then needed to combine the three queries. Q1 contained more results than Q2 and Q3, and where there were no matches in Q2 and/or Q3, I still wanted to return the results from Q1. Similarly where Q2 returned a result Q3 didn't, I wanted to return null results rather than no results, and vice versa.

All of this necessitated a fourth query joining Q1, Q2, and Q3 using LEFT JOINs; but this caused a "multipart identifier cannot be bound" error. This was solved by replacing all of the INNER JOINs in the constituent queries with LEFT JOINS. Essentially, I'd assumed that Q4 would look only at the results of the other queries, whereas it was actually working it all out at once, hence the error.

So, I've solved the actual error, but I'm now running four queries, all using LEFT JOINs. Even though this is returning the same results in each of the constituent queries, it's much slower because of how outer joins work.

Is there any way that I can get my combination query to work as I'd originally expected (i.e. to produce Q1, Q2 and Q3 using INNER JOINS for speed, then have Q4 treat them as flat data when LEFT JOINing them)?

I could use some VBA to drop each of the queries into a temporary table and have Q4 look at those, but I was hoping to do it all in SQL. Can this be done?

Hi @RedFilter - forgive my ignorance, but isn't a schema only relevant in SQL Server? I'm using MS Access 2010. I may be misinterpreting the word. — Sinister Beard
Probably not, but you can use subqueries and such like. SELECT ... FROM (SELECT INNER q1, q2 etc) LEFT Q3 — Fionnuala
@DStanley Thanks. Are the actual table structures and queries relevant though? I'm happy to include them, but I suspect they'd cloud what's essentially a theoretical question; unless the actual table structures and/or query layouts would effect whether or not I could mix INNER and LEFT joins, but I don't think that's the case? — Sinister Beard

NGLN NGLN · Accepted Answer · 2014-02-05T16:41:42

The answer to the title question is: yes, you can mix outer and inner joins:

SELECT
  A.X,
  B.Y,
  C.Z
FROM
  A RIGHT JOIN (
    B INNER JOIN (
      C LEFT JOIN (
        D
      ) ON C.DID = D.ID
    ) ON B.CID = C.ID
  ) ON A.BID = B.ID

The answer to the body question is: no, you cannot inner join tables, and outer join that result as if the inner join would have been calculated as a left join. Once a join is performed, it's result is locked and resembles a whole new set of data.

If you want null-matches from Q1 up to Q4, then you have to outer join Q1 up to Q4. It's that simple.

Can I mix LEFT JOINS and INNER JOINS in Access 2010 SQL?

1 Answers