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.

```
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
;
```

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.

https://platform.twitter.com/widgets.jsAlso this one:

— Christian Jørgensen (@ChrJorgensen) February 15, 2022

A = B and C = D and E = 'constant'

can be replaced with

( A, C, E ) = ( B, D, 'constant' )#IBMi

https://platform.twitter.com/widgets.jsWell, this was my 1st… just been recognized as

— Christian Jørgensen (@ChrJorgensen) February 14, 2022

IBM Champion!!! 😍

Thank you very much @IBMChampions – feeling extremely honored! ❤️#IBMi