Variation on JOIN and NULLs #ibmi #db2fori #SQL

with p(rel1id, a1, a2, a3, x) as (
    values 
        (1, 'yes', 4, 56, 'blue'),
        (2, 'yes', 4, 56, 'red'),
        (3, 'no', 4, 56, 'white'),
        (4, 'no', 4, NULL, 'green')
        
),
q(rel2id, a1, a2, a3, y) as (
    values 
        (5, 'yes', 4, 56, 'cats'),
        (6, 'no', 4, NULL, 'dog')
)
/* Natural join baby */
    -- /* 1 */ select * from p join q using (a1, a2, a3)
/* almost natural */
    -- /* 2 */ select * from p join q on (p.a1, p.a2, p.a3) = (q.a1, q.a2, q.a3)
/* I wish this could work ... RFE maybe...*/
    -- /* proto 3.1 */ select * from p join q on exists (values (p.a1, p.a2, p.a3) INTERSECT values (q.a1, q.a2, q.a3))
    -- /* proto 3.2 */ select * from p join q on (p.a1, p.a2, p.a3) is not distinct from (q.a1, q.a2, q.a3)
/* But here are the workarounds... we're saved! */
    -- /* 3.1 */ select * from p , q where exists (values (p.a1, p.a2, p.a3) INTERSECT values (q.a1, q.a2, q.a3))
    -- /* 3.2 */ select * from p join q on p.a1 is not distinct from q.a1 and p.a2 is not distinct from q.a2 and p.a3 is not distinct from q.a3
;