Databricks与SQL Server中T-SQL JOIN查询结果不一致问题

Databricks SQL执行情况（架构：basedrd） #

查询1：返回144行 #

SELECT DISTINCT
  PSTT.TransactionTypeID
FROM basedrd.TransactionType PSTT
LEFT OUTER JOIN basedrd.PMSTransaction ST
  ON PSTT.TransactionType = ST.TransactionType
WHERE PSTT.TransactionSource = 'Pershing'
OR (PSTT.TransactionSource = '')
AND (ST.SEDOL = 'CASH'
      AND PSTT.IsCashTransaction = 1
      OR ST.SEDOL <> 'CASH'
      AND PSTT.IsCashTransaction = 0)

查询2：无返回行 #

SELECT DISTINCT
  PSTT.TransactionTypeID
FROM basedrd.PMSTransaction ST
LEFT OUTER JOIN basedrd.TransactionType PSTT
  ON PSTT.TransactionType = ST.TransactionType
    AND (PSTT.TransactionSource = 'Pershing'
      OR PSTT.TransactionSource = '')
    AND (ST.SEDOL = 'CASH'
      AND PSTT.IsCashTransaction = 1
      OR ST.SEDOL <> 'CASH'
      AND PSTT.IsCashTransaction = 0)

SQL Server执行情况（架构：dbo） #

查询1：返回145行 #

SELECT DISTINCT
  PSTT.TransactionTypeID
FROM dbo.TransactionType PSTT
LEFT OUTER JOIN dbo.PMSTransaction ST
  ON PSTT.TransactionType = ST.TransactionType
WHERE PSTT.TransactionSource = 'Pershing'
OR (PSTT.TransactionSource = '')
AND (ST.SEDOL = 'CASH'
      AND PSTT.IsCashTransaction = 1
      OR ST.SEDOL <> 'CASH'
      AND PSTT.IsCashTransaction = 0)

查询2：返回46行 #

SELECT DISTINCT
  PSTT.TransactionTypeID
FROM dbo.PMSTransaction ST
LEFT OUTER JOIN dbo.TransactionType PSTT
  ON PSTT.TransactionType = ST.TransactionType
    AND (PSTT.TransactionSource = 'Pershing'
      OR PSTT.TransactionSource = '')
    AND (ST.SEDOL = 'CASH'
      AND PSTT.IsCashTransaction = 1
      OR ST.SEDOL <> 'CASH'
      AND PSTT.IsCashTransaction = 0)

两次执行仅数据库架构不同（Databricks使用basedrd，SQL Server使用dbo），但结果存在显著差异：

• 相同逻辑的查询1在Databricks返回144行，SQL Server返回145行
• 查询2在Databricks无返回结果，在SQL Server返回46行

内容的提问来源于stack exchange，提问作者Patterson