Follow me for a little SQL “Amuse-gueule” and spot the difference.
Before you jump to your favorite SQL editor (ACS? DBeaver? Other?) and un-comment/run the sections one after an other, read first and ponder how similar these are and only differ on some subtile but fundamental aspects.
Use the comment section to list the differences you found in the resulting sets, share what came as a surprise, what you learned, and rave on the select * from p … q … pattern.
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
;
Was this post of any interest? Then go visit @ChrJorgensen and wish him a nice IBM Champion year! This post is a follow-up on his reply.
Also this one:
A = B and C = D and E = ‘constant’
can be replaced with
( A, C, E ) = ( B, D, ‘constant’ )#IBMi
— Christian Jørgensen (@ChrJorgensen) February 15, 2022 https://platform.twitter.com/widgets.js
Well, this was my 1st… just been recognized as
IBM Champion!!!
Thank you very much @IBMChampions – feeling extremely honored! #IBMi
— Christian Jørgensen (@ChrJorgensen) February 14, 2022 https://platform.twitter.com/widgets.js
