Por que o SQL Server exige LEFT JOIN para recuperar um status que não existe?

Você precisa aninhar suas junções. Caso contrário, o que está acontecendo é que ele espera que cada cláusula de junção individual retorne um resultado, mas não poderá se a anterior não retornar nada.

Essentially, you want the server to take the result of the inner-join of Instance and Status, and left-join all of that back to Server.

SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
FROM Server as srv
  JOIN Status as srvst
    ON srv.ServerStatusID = srvst.StatusID
  LEFT JOIN (
      Instance as ins
      JOIN Status as insst
        ON ins.InstanceStatusID = insst.StatusID
    )
    ON srv.ServerID = ins.ServerID
  LEFT JOIN (
      Database as dbs
      JOIN Status as dbsst
        ON dbs.DatabaseStatusID = dbsst.StatusID
    )
    ON ins.InstanceID = dbs.InstanceID;

In SQL Server, the parenthesis are not essential, they are purely for readability. The key is putting JOIN table2 ON ... inside another join, so it comes out to LEFT JOIN table1 JOIN table2 ON ... ON ... ie the ON clauses are nested.

This is the exact equivalent of using derived subqueries:

SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  ins.StatusName,
  DatabaseName,
  dbs.StatusName
FROM Server as srv
  JOIN Status as srvst
    ON srv.ServerStatusID = srvst.StatusID
  LEFT JOIN (
      SELECT ins.*, insst.StatusName
      FROM Instance as ins
      JOIN Status as insst
        ON ins.InstanceStatusID = insst.StatusID
    ) ins
    ON srv.ServerID = ins.ServerID
  LEFT JOIN (
      SELECT dbs.*, dbsst.StatusName
      Database as dbs
      JOIN Status as dbsst
        ON dbs.DatabaseStatusID = dbsst.StatusID
    ) dbs
    ON ins.InstanceID = dbs.InstanceID;

Porque a junção com a tabela de status para os itens filhos está acontecendo com base em ins.InstanceStatusID, dbs.DatabaseStatusID que acaba sendo NULL quando as junções anteriores ocorrem em algumas linhas.

Quando você faz uma junção interna posteriormente, ele mostra as linhas correspondentes em ambas as tabelas e para obter também as linhas com valores nulos, você terá que usar a junção à esquerda.

Para visualizar o mesmo:

SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,

  DatabaseName,

  ins.InstanceStatusID,
  dbs.DatabaseStatusID 
  
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID 

SELECT TMP.* ,    insst.StatusName,
  dbsst.StatusName FROM (SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,

  DatabaseName,

  ins.InstanceStatusID,
  dbs.DatabaseStatusID 
  
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID ) AS TMP

    /* Here if left join is not used values which are null are skipped and only values which are in both tables are returned */

    JOIN [Status] as insst
      ON TMP.InstanceStatusID = insst.StatusID 
    JOIN [Status] as dbsst
      ON TMP.DatabaseStatusID = dbsst.StatusID ;

Que depois de usar a junção à esquerda extrai todos os registros do conjunto de resultados esquerdo e os valores correspondentes de ambas as tabelas unidas ao conjunto de resultados

SELECT TMP.* ,    insst.StatusName,
  dbsst.StatusName FROM (SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,

  DatabaseName,

  ins.InstanceStatusID,
  dbs.DatabaseStatusID 
  
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID ) AS TMP
    
    LEFT JOIN [Status] as insst
      ON TMP.InstanceStatusID = insst.StatusID 
    LEFT JOIN [Status] as dbsst
      ON TMP.DatabaseStatusID = dbsst.StatusID ;

Why does SQL Server require a LEFT JOIN on the Status table for child items that do not exist and for the query to display these items?

It doesn't, in general. It is just one of the ways to express your requirement that produces the results you are after.

Your query tries to (inner) join from a null-extended row (created by an outer join) to the Status table using a join predicate that looks for an equality match on StatusID values.

This predicate does not return true because StatusID is null on one side, there is no matching null in the Status table, and the equality predicate would reject null matches anyway. Since the join predicate fails and you have specified an inner join, no result row is produced.

For clarity, the inner join to Status could produce a result with a different join predicate.

For example, there could be a null StatusID in the Status table (something your schema allows) and the join predicate could use IS NOT DISTINCT FROM or an equivalent test where nulls match. You could also use COALESCE or ISNULL in the join predicate to select a particular status (as a default) without adding any new status rows.

There are many possibilities; the point is you could write a join predicate that would produce a match. The one you have does not.

It is possible you thought SQL Server would skip the join to Status for any rows previously null-extended by an outer join, but things don't work that way. An outer join is not an 'optional' join with subsequent short-circuiting. Perhaps some people have a mental image of them functioning that way.

Making any following joins 'outer' as a way to fix the output is close to using 'magic'—a recipe that works but isn't understood.

Why does it work here? Because, with a left outer join, the null-extended row is preserved despite there being no match in Status according to the join predicate. The missing status columns are populated with null, as usual.

An alternative solution

People do seem to find left outer joins more intuitive to work with, but there's no particular reason to prefer them otherwise. You don't have to start at the top of the hierarchy and 'join down' to rows that may or may not be present.

Let's see what happens with your schema if we instead start at the bottom of the hierarchy (the [Database] table) and work up. The first join to Status is straightforward:

SELECT
    DBS.DatabaseName,
    DBSST.StatusName
FROM dbo.[Database] AS DBS
JOIN dbo.[Status] AS DBSST
    ON DBSST.StatusID = DBS.DatabaseStatusID;

Moving up the hierarchy, we now need to add rows from the Instance table. We can't use a left outer join or inner join because there may be an instance without a database. The natural solution is to use a right join, with the associated inner join to Status:

SELECT
    INS.InstanceName,
    INSST.StatusName,
    DBS.DatabaseName,
    DBSST.StatusName
FROM dbo.[Database] AS DBS
JOIN dbo.[Status] AS DBSST
    ON DBSST.StatusID = DBS.DatabaseStatusID
RIGHT JOIN dbo.Instance AS INS
    ON INS.InstanceID = DBS.InstanceID
JOIN dbo.[Status] AS INSST
    ON INSST.StatusID = INS.InstanceStatusID;

There is no need for 'nested' joins here. We quite naturally add in any instances without a database, adding null-extended database rows as necessary. All instances are accounted for, so the inner join to Status is perfectly correct.

We follow the same approach to add in rows from the Server table, along with its status:

SELECT
    SRV.ServerName,
    SVS.StatusName,
    INS.InstanceName,
    INSST.StatusName,
    DBS.DatabaseName,
    DBSST.StatusName
FROM dbo.[Database] AS DBS
JOIN dbo.[Status] AS DBSST
    ON DBSST.StatusID = DBS.DatabaseStatusID
RIGHT JOIN dbo.Instance AS INS
    ON INS.InstanceID = DBS.InstanceID
JOIN dbo.[Status] AS INSST
    ON INSST.StatusID = INS.InstanceStatusID
RIGHT JOIN dbo.[Server] AS SRV
    ON SRV.ServerID = INS.ServerID
JOIN dbo.[Status] AS SVS
    ON SVS.StatusID = SRV.ServerStatusID;

This produces the results you want, without invoking any magic.

There are any number of ways to write a correct query specification for your requirement. I show a RIGHT JOIN alternative mostly because people seem somewhat scared by right outer joins or consider them redundant.