Postgresql 12. In a function returning a SELECT result of table A and B, when the function input Boolean parameter is true, do
select * from A INNER join B on A.id = B.id;
when the parameter is false, do
select * from A LEFT join B on A.id = B.id;
checked case-when-end but it doesn't work in "where". Is there any way to achieve this without writing two separate SELECT?
A LEFT JOIN in which the non-nullable column of right table is forced to be NOT NULL in the result is equivalent to INNER JOIN.
SELECT *
FROM A LEFT JOIN B
ON A.id = B.id
WHERE NOT param -- allow LEFT JOIN behavior
OR B.id IS NOT NULL -- force INNER JOIN behavior
;
Test 1:
WITH A (id) AS (
VALUES (1), (2), (3)
)
, B (id) AS (
VALUES (1), (3)
)
, args (isInner) AS (
SELECT true
)
SELECT *
FROM A LEFT JOIN B
ON A.id = B.id
WHERE (SELECT NOT isInner FROM args)
OR B.id IS NOT NULL
;
Result (INNER JOIN):
Test 2:
WITH A (id) AS (
VALUES (1), (2), (3)
)
, B (id) AS (
VALUES (1), (3)
)
, args (isInner) AS (
SELECT false
)
SELECT *
FROM A LEFT JOIN B
ON A.id = B.id
WHERE (SELECT NOT isInner FROM args)
OR B.id IS NOT NULL
;
Result (LEFT JOIN):
