更新后的SQL查询即使使用交叉联接也不返回数据。

Question

因此，我一直在替换应用程序中的所有旧查询，以删除*=操作符。在其中一个查询中，我很难弄清楚为什么输出是不同的。

这是一个古老的问题：

 SELECT 
    URMS_User_Name AS FE_Name,
    URMS_User_ID AS FE_Code, 
    COUNT( MRFS_FE_Code ) AS No_Of_Jobs, 
    SCRM_Service_Center_Name AS Service_Center_Name, 
    CSMSDVLP.fnGetExpertise( URMS_User_ID ) AS Expertise 
 FROM 
    TBL_User_Master, 
    TBL_Service_Center_Master, 
    TBL_User_Role, 
    TBL_MR_FE_Schedule 
 WHERE                
    USRL_Role_Code = 'FE' AND
    URMS_User_ID = USRL_User_ID AND      
    SCRM_Service_Center_ID = URMS_Service_Center_ID AND
    URMS_User_ID *= MRFS_FE_Code AND
    MRFS_Scheduled_Date >= '28-Oct-2014 00:00:00' AND 
    MRFS_Scheduled_Date <= '04-Nov-2014 00:00:00' 
 GROUP BY 
    SCRM_Service_Center_Name,
    URMS_User_Name,
    URMS_User_ID 
 ORDER BY 
    URMS_User_ID

这是更新的查询：

SELECT 
    URMS_User_Name AS FE_Name,
    URMS_User_ID AS FE_Code,
    COUNT( MRFS_FE_Code ) AS No_Of_Jobs,
    SCRM_Service_Center_Name AS Service_Center_Name, 
    CSMSDVLP.fnGetExpertise( URMS_User_ID ) AS Expertise 
FROM 
    TBL_User_Master 
    Left Join TBL_MR_FE_Schedule 
       ON URMS_User_ID = MRFS_FE_Code 
    Inner Join TBL_Service_Center_Master 
       ON SCRM_Service_Center_ID = URMS_Service_Center_ID 
    Inner Join TBL_User_Role 
       ON URMS_User_ID = USRL_User_ID 
WHERE 
    USRL_Role_Code = 'FE'
    AND MRFS_Scheduled_Date >= '28-Oct-2014 00:00:00' 
    AND MRFS_Scheduled_Date <= '04-Nov-2014 00:00:00' 
GROUP BY 
    SCRM_Service_Center_Name,
    URMS_User_Name, 
    URMS_User_ID 
ORDER BY 
    URMS_User_ID

旧的查询很好地返回数据，但是新的查询返回空行。我试着用交叉连接和相同的问题替换所有的联接。

不过，奇怪的是，如果我在没有日期条件的情况下运行查询，一切似乎都正常。所以，现在，我搞不懂是我犯了一个错误，还是那个老操作员表现得很疯狂。

Answer 1

外部连接表的条件属于on子句。外部连接记录的MRFS_Scheduled_Date为NULL，所以使用WHERE子句删除这些记录，从而将外部连接转换为内部连接。由于WHERE子句比以前限制性更强，这可能是没有得到任何结果的原因。

SELECT 
  URMS_User_Name AS FE_Name, 
  URMS_User_ID AS FE_Code, 
  COUNT( MRFS_FE_Code ) AS No_Of_Jobs, 
  SCRM_Service_Center_Name AS Service_Center_Name, 
  CSMSDVLP.fnGetExpertise( URMS_User_ID ) AS Expertise 
FROM TBL_User_Master 
LEFT JOIN TBL_MR_FE_Schedule ON URMS_User_ID = MRFS_FE_Code AND MRFS_Scheduled_Date >= '28-Oct-2014 00:00:00' AND MRFS_Scheduled_Date <= '04-Nov-2014 00:00:00' 
INNER JOIN TBL_Service_Center_Master ON SCRM_Service_Center_ID = URMS_Service_Center_ID 
INNER JOIN TBL_User_Role ON URMS_User_ID = USRL_User_ID 
WHERE USRL_Role_Code = 'FE'  
GROUP BY SCRM_Service_Center_Name, URMS_User_Name, URMS_User_ID 
ORDER BY URMS_User_ID;