为什么 SQL Server 需要 LEFT JOIN 来检索不存在的状态？

您需要嵌套连接。否则，会发生的情况是，它期望每个单独的连接子句返回一个结果，但如果前一个连接子句没有返回任何结果，则它不能返回结果。

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;

因为子项与状态表的联接是基于 ins.InstanceStatusID、dbs.DatabaseStatusID 进行的，当其之前的联接发生在某些行中时，结果为 NULL。

当您稍后执行内部联接时，它会显示两个表中匹配的行，并且要获取具有空值的行，您将必须使用左联接。

可视化相同的：

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 ;

使用左连接后，会从左结果集中提取所有记录，并从连接到结果集的两个表中提取匹配值

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.