LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

[RGSLCA]

Hi,

I may need to investigate further but this is my observation,

I had a SQL query working fine until today,

Query-A
SELECT ta.id,tb.id
FROM TableA ta
     left join
     TableB tb
     on ta.id = tb.id
     and ta.dtx = to_date(<date string>)
where
     ta.some_other_col = true

Query-B
SELECT ta.id,tb.id
FROM TableA ta
     left join
     TableB tb
     on ta.id = tb.id
where
     ta.dtx = to_date(<date string>)
     and
     ta.some_other_col = true

Option-A Query started not filtering rows and sending different dates without considering the date filter

I had to modify the SQL to be like Option-B to make the filter working.

I have tested on Oracle and Other platforms this seems to work fine. Can someone tell me what am I doing wrong or is there a reason for this ? it looked like a sudden change but I cant believe that as a reason.

Note: The original query was a CTE with multiple steps like above.

[data_pulse · Accepted solution]

@RGSLCA 

The difference here is simply where the date filter is applied, especially since ta.dtx belongs to the left table.

In Query A, the date condition is part of the LEFT JOIN. It only controls whether a matching row from TableB is returned. Rows from TableA with other dates are still preserved, but with tb.id = NULL.

In Query B, moving the date condition to WHERE actually filters TableA, giving you the intended result.

I tried reproducing your scenario with sample data in Databricks Serverless SQL:

• Query A: Returned IDs 1, 2 and 4, including other dates with tb.id = NULL.
• Query B: Returned only ID 1, matching the requested date.

Interestingly, I also ran Query A in Microsoft SQL Server, and it returned exactly the same results. Same result with PostgreSQL too.

The Databricks Photon execution plan confirmed this behaviour too, showing a LeftOuter join with the date restriction as part of the join condition.

So this looks like standard SQL LEFT JOIN behaviour rather than a recent Databricks change.

If your intention is to filter TableA by date while still preserving unmatched rows from TableB, I'd recommend Query B, or filtering TableA in a CTE before joining.

References:

• Databricks JOIN documentation

[RGSLCA]

Thanks! , let me revisit this! appreciate it!

[RGSLCA]

Absolutely, correct, there have been a filter for preventing NULLs flowing through.
Oracle also producing same,

[data_pulse]

@RGSLCA 

Nice that you observed this in oracle too. Good to see the behaviour is consistent across platforms.