How to write this Conditional where clause in SQl Server?

Question

Hi I have the following type of query:

Select a,b from tab1 whre tab1.id in 
 (Select id from tab2 where x=1 and y=2 and  z>getdate())

Now z is a datetime column in tab2 and it is nullable column.So I want the check z > getdate() only if it is not null. Checked Conditional WHERE clause in SQL Server but could not do it accurately.Basically I dont want the 'z' column check in the above query if it is null in tab2.

Thanks in advance.

HoneyBadger · Answer 1 · 2016-01-11T12:03:12.220

2

Did you mean:

Select a,b from tab1 where tab1.id in 
 (Select id from tab2 where x=1 and y=2 and  (z is null or (z is not null and z>getdate())))

edited Jan 11 '16 at 12:03

answered Jan 11 '16 at 10:27

HoneyBadger

14,750
3
34
48

Actually, I don't think the `z is not null` check is necessary, but it helps me see things clearly – HoneyBadger Jan 11 '16 at 10:28

David Rushton · Answer 2 · 2016-01-11T11:18:07.413

I think you need a combination of AND and OR operators. When combining these you need to be aware of operator precedence. In my example I've used brackets to ensure that x, y AND z are checked together, with z then rechecked separately. The query returns any record where check block 1 OR 2 is true.

In my example I've used a JOIN but you can apply this same technique to your original sub query.

Example

SELECT
    t1.a,
    t1.b
FROM
    Tab1 AS t1
        INNER JOIN Tab2 AS t2            ON t1.Id = t2.Id
WHERE
    (
        -- Check block 1.
        t2.x = 1
        AND t2.y = 1
        AND t2.z > GETDATE()                
    )
    -- Check block 2.
    OR z IS NULL
;

EDIT: I'm not entirely sure I understand the question. If you could provide some sample records and the expected output that would help.

How to write this Conditional where clause in SQl Server?

2 Answers2