Issue
I have a SQL statement that does left join with a table:
select a.id, b.col1 from tableA a
left join (select col1 from tableB where date = 'xxx') b on a.id = b.id
For some application constraint (I need to use Spring JPQL query that does not permit subquery), I need to "flatten" this query to remove the subquery without changing the meaning of the query: I want to enrich tableA with a subset of tableB.
I have tried a few queries such as:
select a.id, b.col1 from tableA a
left join tableB b on a.id = b.id
where (date = 'xxx' or date is null)
But that gave me different set of answer from previous query.
How do I remove this subquery?
Solution
It can be done in multiple different ways – using cte, using joins
Using join it can be implemented as –
select a.id, b.col1 from tableA a left join tableB b on a.id = b.id and b.date = 'xxx'
using CTE it can be implemented as –
with t as
(
select col1, id from tableB where date = 'xxx'
)
select a.id, b.col1 from tableA a
left join t on a.id = t.id
Answered By – Amrita Srivastava
Answer Checked By – David Marino (BugsFixing Volunteer)