Return NULL for CTE where records not found

September 23, 2024

I have this SQL query using a Common Table Expression:

with a as (
    select top 1 flow_id
    from flow
    where invoice_id > 0
), b as (
    select '45' as col2
)
select *
from a, b

if none of the rows satisfy the condition invoice_id>0 in the flow table, then I get the following result:

I want the following result even if no records are found from flow table:

Can this be done?

>Solution :

You can do this:

with a as (
    select top 1 flow_id
    from (values(1,-1)) as flow (flow_id,invoice_id)
    where invoice_id > 0
), b as (
    select '45' as col2
)
select *
from a
right join b on 1 = 1

So, use a right join making sure any row on the right side table is in irrespective of whether a row on the left side is found based on the join condition. In this case, there is no real linking as you did a CROSS JOIN effectively, so the condition is 1=1 to reflect that.