How to remove this subquery from this SQL statement?-CodePudding

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?

CodePudding user response：

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