title: Why left join turns into inner join if inner join is included in the query?
body: <pre><code>    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
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Jx4wQ.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Jx4wQ.png" alt="Sql results for both queries"></a></p>
  
  <p>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.</p>
  
  <p>The only solution that I found is joining a subquery in left join, however, I don't understand how it's different.</p>