Thursday, February 4, 2016

SQL WHERE Clause Abnormality

I've been working with SQL Server for a long time (about 10 years) and I recently found out that in all that time I completely misunderstood something about WHERE clauses.  I've always written my WHERE clauses with a healthy dose of parentheses in order to logically separate the conditions, particularly when an OR condition was involved joining multiple sets of complex AND connected conditions.  Apparently you don't have to do that.  Check out the example below to see what I mean.

CREATE TABLE #temp (
     ID INT
    ,StartDate DATETIME2
    ,EndDate DATETIME2
)

DECLARE @Today DATE = CONVERT(DATEGETDATE())
DECLARE @Tomorrow DATE = CONVERT(DATEDATEADD(dd, 1, @Today))

INSERT INTO #temp (ID, StartDate, EndDate)
VALUES (1, @Today, @Tomorrow), (2, DATEADD(dd, -2, @Today), @Today), (3, '01/01/2014''12/31/2015'), (4, @Today, NULL), (4, @Today, '12/31/2016')

SELECT *
FROM #temp
WHERE
    StartDate >= @Today
    AND EndDate <= @Tomorrow
    OR ID = 4
    AND (
        StartDate >= @Today
        AND EndDate IS NULL
    )


Prior to this realization I thought the above query would pull back the following:

  • Any records with a StartDate in the future and an EndDate in the past
  • Any records with a StartDate in the future and a NULL EndDate
  • Any records with an ID of 4
What I've found, though, is that the OR causes a switch to happen so that only the following are pulled back:
  • Any records with a StartDate in the future and an EndDate in the past
  • Any records with an ID of 4 and a StartDate in the future and a NULL EndDate
I still don't like the way it reads.  I'd still rather see it written like this:

SELECT *
FROM #temp
WHERE
    (
        StartDate >= @Today
        AND EndDate <= @Tomorrow
    ) OR (
        ID = 4
        AND StartDate >= @Today
        AND EndDate IS NULL
    )


But it is good to know the other way is legal.

No comments:

Post a Comment