CODEWITHSUNDEEP
google-bigquery
Vishal John
Vishal John

Reputation: 4382

Comparing fields only when NOT null

I have a query like this.

SELECT T1.timestamp, T2.timestamp FROM (SELECT …) AS T1 LEFT OUTER JOIN (SELECT ….) AS T2 ON T1.id = T2.id WHERE T1.timestamp < T2.timestamp

The problem I am facing is that the condition WHERE T1.timestamp < T2.timestamp will filter out rows where T1.timestamp < T2.timestamp as well as rows where T2.timestamp is null.

In my use case I don't want the query to filter out rows where T2.timestamp is null. T1.timestamp < T2.timestamp should compare only if both timestamps are defined and should return rows where T2.timestamp is null.

Is there a way to achieve this?

Upvotes: 1

Views: 1632

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

Copying N.N. answer here so the question doesn't show up with 0 answers:

WHERE T1.timestamp<= ifnull(T2.timestamp,T1.timestamp)

And Pentium10 answer:

WHERE T1.timestamp < T2.timestamp OR T2.timestamp IS NULL

(I'll delete this answer if N.N. or Pentium10 move their comments to an answer)

Upvotes: 2

Related Questions