如何用最简SQL Server查询实现按ID取值或全量查询？

针对你的需求，只需修改WHERE子句即可用单一查询满足两种场景，以下是两种最简写法：

写法一：逻辑或判断（可读性优先） #

DECLARE @EventLogId int = NULL

SELECT 
    EL.EventLogId,
    EL.EventLogDateTime,
    EL.Username,
    EL.EventLogTypeId,
    EL.ActionTypeId,
    EL.ProfessionalId,
    EL.BioId,
    EL.[Message],
    EL.ExceptionMessage,
    EL.StackTrace,
    ELTypes.[Name] AS EventLogTypeName,
    ATypes.[Name] AS ActionTypeName
FROM 
    dbo.EventLog EL
INNER JOIN 
    EventLogType ELTypes ON ELTypes.Id = EL.EventLogTypeId
INNER JOIN 
    ActionTypes ATypes ON ATypes.ActionTypeId = EL.ActionTypeId
WHERE 
    @EventLogId IS NULL OR EL.EventLogId = @EventLogId

写法二：ISNULL函数简化（代码更紧凑） #

DECLARE @EventLogId int = NULL

SELECT 
    EL.EventLogId,
    EL.EventLogDateTime,
    EL.Username,
    EL.EventLogTypeId,
    EL.ActionTypeId,
    EL.ProfessionalId,
    EL.BioId,
    EL.[Message],
    EL.ExceptionMessage,
    EL.StackTrace,
    ELTypes.[Name] AS EventLogTypeName,
    ATypes.[Name] AS ActionTypeName
FROM 
    dbo.EventLog EL
INNER JOIN 
    EventLogType ELTypes ON ELTypes.Id = EL.EventLogTypeId
INNER JOIN 
    ActionTypes ATypes ON ATypes.ActionTypeId = EL.ActionTypeId
WHERE 
    EL.EventLogId = ISNULL(@EventLogId, EL.EventLogId)

逻辑说明 #

• 当@EventLogId为NULL时：
  • 写法一中@EventLogId IS NULL条件成立，查询返回所有符合JOIN条件的记录；
  • 写法二中ISNULL(@EventLogId, EL.EventLogId)会返回EL.EventLogId本身，相当于EL.EventLogId = EL.EventLogId（恒成立），同样返回所有记录。
• 当@EventLogId有具体值时：两种写法都会匹配EL.EventLogId等于该值的单条记录。

原查询问题说明 #

你原来的WHERE条件EL.EventLogId = @EventLogId AND NOT(EL.EventLogId IS NULL)存在逻辑错误：SQL中NULL与任何值用=比较结果都是未知，因此当@EventLogId为NULL时，EL.EventLogId = @EventLogId永远不成立，导致查询无结果，无法满足“返回全部记录”的需求。

内容的提问来源于stack exchange，提问作者David Lerma Developer