Inner join only for not empty values

Question

i can get right result using the where join , with next sql:

WITH fktable1 AS (
SELECT 2 AS id
)
,fktable2 AS (
SELECT 3 AS id
)
SELECT main.*
FROM
-- main data
 (
 VALUES
 (1, NULL, NULL),
 (2, 2, NULL),
 (3, NULL, 3),
 (4, 2, 3),
 (5, 4, NULL), -- wrong, not exist fkcol1 in the fktable1
 (6, NULL, 5) -- wrong, not exist fkcol2 in the fktable2
 ) AS main (col1, fkcol1, fkcol2)
 , fktable1, fktable2
WHERE 
 (
 fktable1.id = main.fkcol1
 OR main.fkcol1 is NULL
 )
 AND (
 fktable2.id = main.fkcol2
 OR main.fkcol2 is NULL
 );

it give me the output:

 col1 | fkcol1 | fkcol2 
------+--------+--------
 1 | | 
 2 | 2 | 
 3 | | 3
 4 | 2 | 3
(4 rows)

is there some way to get the same by table join statement? i tried to find solution and read many exists question, but unsuccess.

Answer 1

You have an inner join with a condition there:

WITH fktable1 (id) AS (
 values (2)
)
,fktable2 (id) AS (
 values (3)
)
SELECT main.*
FROM (
 VALUES
 (1, NULL, NULL),
 (2, 2, NULL),
 (3, NULL, 3),
 (4, 2, 3),
 (5, 4, NULL), 
 (6, NULL, 5) 
) AS main (col1, fkcol1, fkcol2)
 join fktable1 f1 on f1.id = main.fkcol1 or main.fkcol1 is null
 join fktable2 f2 on f2.id = main.fkcol2 or main.fkcol2 is null

Answer 2

A slight variation from a_horse_with_no_name 's answer is:

WITH fktable1 (id) AS (
 values (2)
)
,fktable2 (id) AS (
 values (3)
)
SELECT main.*
FROM (
 VALUES
 (1, NULL, NULL),
 (2, 2, NULL),
 (3, NULL, 3),
 (4, 2, 3),
 (5, 4, NULL), 
 (6, NULL, 5) 
) AS main (col1, fkcol1, fkcol2)
 join fktable1 f1 
 on coalesce(main.fkcol1, f1.id) = f1.id
 join fktable2 f2 
 on coalesce(main.fkcol2, f2.id) = f2.id

The ON clause is a bit shorter but perhaps not as obvious. The idea is that if main.fkcol1 is null, f1.id is compared with itself. Note that this is not valid if id can be null, so a_horse_with_no_name 's solution is a bit safer as well.

To make it null-safe, a construction like:

coalesce(main.fkcol1, f1.id, -1) = coalesce(f1.id, -1)

or:

coalesce(main.fkcol1, f1.id) IS NOT DISTINCT FROM f1.id

can be used