Why left join turns into inner join if inner join is included in the query?

Question

    IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Atbl')
 DROP TABLE Atbl

CREATE TABLE ATbl
 (
    Id int unique,
    AName varchar(20),
 )

 GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Btbl')
    DROP TABLE Btbl

CREATE TABLE BTbl
 (
    Id int unique,
    BName varchar(20),
    ATblId int
 )

GO

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Ctbl')
    DROP TABLE Ctbl

CREATE TABLE CTbl
 (
    Id int unique,
    CName varchar(20),
    BTblId int
 )

GO

TRUNCATE TABLE Atbl
TRUNCATE TABLE Btbl
TRUNCATE TABLE Ctbl


INSERT INTO Atbl VALUES (1, 'Name1')
INSERT INTO Atbl VALUES (2, 'Name2')
INSERT INTO Atbl VALUES (3, 'Name3')

INSERT INTO Btbl VALUES (1, 'Name1', 2)
INSERT INTO Btbl VALUES (2, 'Name2', 3)

INSERT INTO Ctbl VALUES (1, 'Name2', 2)

select * from atbl
left join btbl on btbl.atblid=atbl.id
inner join ctbl on ctbl.btblid=btbl.id

select * from atbl
left join 
(select btbl.id, btbl.atblid from btbl
inner join ctbl on ctbl.btblid=btbl.id) a
on atbl.id=a.atblid

Why one inner join in query turns the all query into inner join. The first query joins TblA -(LEFT JOIN)-> TblB -> (INNER JOIN) -> TblC = The whole query is inner joined.

The only solution that I found is joining a subquery in left join, however, I don't understand how it's different.

Answer 1

This is a common behaviour in database implementations, due to the implications of join nesting. A series of left joins followed by an inner join (or a CROSS APPLY instead of an OUTER APPLY) will have this outcome.

To avoid this, you've already hit on the solution:

select * from atbl
left join 
    (select btbl.id, btbl.atblid 
        from btbl
        inner join ctbl on ctbl.btblid=btbl.id) a
        on atbl.id=a.atblid

This is a non-correlated subquery, as you've not referenced ATBL inside the brackets - meaning the engine can select a reasonably good join strategy for it, or compute the entire subquery once rather than row-by-row.

Another option is to change all the table joins to left joins:

select * from atbl
    left join btbl on btbl.atblid=atbl.id
        left join ctbl on ctbl.btblid=btbl.id
WHERE
     -- Rows where the first LEFT is not satisfied, or BOTH are satisfied.
     (btbl.atblid IS NULL OR ctbl.btblid IS NOT NULL)

You can then use the WHERE clause to filter where either neither of the joins from B onward were hit (i.e. either I didn't find a B or I found both a a B and a C).