topic Re: LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER in Data Engineering

LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

RGSLCA — Wed, 28 Oct 2026 23:17:06 GMT

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.

Re: LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

data_pulse — Thu, 29 Oct 2026 00:13:02 GMT

@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

Re: LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

RGSLCA — Thu, 29 Oct 2026 01:15:57 GMT

Thanks! , let me revisit this! appreciate it!

Re: LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

RGSLCA — Fri, 30 Oct 2026 15:29:09 GMT

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

Re: LEFT JOIN WITH FILTER vs LEFT JOIN and WHERE FILTER

data_pulse — Fri, 30 Oct 2026 15:40:25 GMT

@RGSLCA

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