1

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.

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
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.