Share via

Mishandling of outer joins with column = value conditions

Nick North 0 Reputation points
2025-12-16T20:52:16.1666667+00:00

I understand that potential Access bugs can be reported here, and an MVP will pass them to Microsoft if they are validated. I'd be grateful if someone could do that with this one please.

There is a problem in Access 365 editing certain outer join queries in SQL view. Access requires brackets to make the query work, but then strips those brackets out if you save and reopen the query, meaning that attempts to edit it fail due to missing brackets (only the editor doesn't tell you that's the problem).

This LeftJoin.accdb database shows the problem, and here is a description. The database contains RootTable, a table with ID and TextField columns, and JoinedTable, a table including a RootId column which links to RootTable.ID, a TextField and a NumberField.

First the ColumnLeftJoin query is a simple left join query joining on a couple of columns:

SELECT ...
FROM
RootTable
LEFT JOIN JoinedTable ON (RootTable.ID = JoinedTable.RootId)
AND (RootTable.TextField = JoinedTable.TextField);

(Note that the brackets are not necessary, but the editor inserts them if you save and reopen the query.) This query works fine: you can run it, save it, reopen it, make edits, and save again.

Then the ValueLeftJoin query is a left join query in which one of the join conditions checks for equality of a column with a fixed numeric value:

SELECT ...
FROM
RootTable
LEFT JOIN JoinedTable ON RootTable.ID = JoinedTable.RootId
AND JoinedTable.NumberField = 1;

This query runs but, if you make a trivial edit like deleting and reinstating the final semicolon, then saving gives a "JOIN expression not supported" error. It will only save if you add extra brackets around the join condition:

SELECT ...
FROM
RootTable
LEFT JOIN JoinedTable ON (RootTable.ID = JoinedTable.RootId
AND JoinedTable.NumberField = 1);

If you then reopen it after saving, the brackets have disappeared again. This makes editing very frustrating, as most edits fail for no obvious reason, especially if you are looking at someone else's query and don't know about this quirk.

There are further, probably related, weirdnesses. For example, the BadValueLeftJoin query swaps around the two parts of the join condition:

SELECT ...
FROM
RootTable
LEFT JOIN JoinedTable ON JoinedTable.NumberField = 1
AND RootTable.ID = JoinedTable.RootId;

This always gives a "Query cannot be executed" error, even after adding brackets.

This might be an editor problem, but feels more like a bug in parsing Access SQL when one part of a comparison is a value rather than a column reference.

Microsoft 365 and Office | Access | Development
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 11,531 Reputation points Volunteer Moderator
    2025-12-16T21:03:22.7733333+00:00

    Hi Nick,

    Can you also please post a screenshot of your Access build version and update channel? Thanks!

  2. George Hepworth 22,295 Reputation points Volunteer Moderator
    2025-12-16T21:28:07.4333333+00:00

    This query doesn't execute, as you say, but not because of any problem in the join. 

    SELECT    RootTable.ID,    RootTable.TextField,    JoinedTable.TextField,    JoinedTable.NumberFieldFROM    RootTable    LEFT JOIN JoinedTable ON JoinedTable.NumberField = 1    AND RootTable.ID = JoinedTable.RootId;

    Rather, it's syntactically invalid, as the error message indicates. This is true regardless of the use of parentheses. Note that the Monaco editor helpful highlighted your syntax error.

    User's image

    Your SQL tries to join on a field "JoinedTable.NumberField" and a literal value, "1". There is nothing there to join on, so that's invalid SQL and it's not how joins work. The second part of your Join statement doesn't change that. The SQL fails for the same reason because the problem is in the first part.
    You COULD use the evaluation of 

    [JoinedTable].[NumberField] = 1

    in a where clause, if that's the logic you need:

    SELECT
    RootTable.ID,
    RootTable.TextField,
    JoinedTable.TextField AS Expr1,
    JoinedTable.NumberField AS Expr2
FROM
    RootTable
    LEFT JOIN JoinedTable ON RootTable.ID = JoinedTable.RootID
WHERE
    [JoinedTable].[NumberField] = 1;

    In short, the problem is not in the Access SQL editor, nor in parentheses (or brackets as they are also known). The problem is in the logic of the SQL Syntax. What is supported and what is not supported.

  3. Ken Sheridan 3,551 Reputation points
    2025-12-17T13:39:41.6266667+00:00

    George, other than testing for NULL, restricting a query on a column on the right side of a LEFT OUTER JOIN will, in effect, turn the join into an INNER JOIN. To do so the join should be to a subquery in which the restriction is undertaken:

    SELECT
    RootTable.ID,
    RootTable.TextField,
    JT.TextField,
    JT.NumberField
FROM
    RootTable
    LEFT JOIN 
        (SELECT RootID,TextField,NumberField
		 FROM JoinedTable
 		 WHERE NumberField = 1) AS JT
ON RootTable.ID = JT.RootID;

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.