0

Actually might be a simple question:

Given 2 tables:

table1: A,B,C
(A1,B1,28471),
(A1,B2,01244),
(A2,B1,1283a),
(A2,B2,82r7e);

table2: A,B,D,E,F,G
(A1,B1,18,1,6,8),
(A2,B2,18,2,3,0),
(A3,B1,18,7,1,4),
(A4,B2,18,1,9,6);

Will the following statement result in a the following result given the example tables:

SELECT E,F,G FROM table2 WHERE (A,B) IN (SELECT A,B FROM table1)

expected result:

 E F G
(1,6,8), -- (A1,B1)
(2,3,0); -- (A2,B2)

Can someone confirm this is a valid method to select based on tuples?

asking as the most likely next step is to UPDATE the E field by adding +5 for matching results

0
2

Consider EXISTS instead:

SELECT E,F,G
FROM   table2 t2
WHERE  EXISTS (
   SELECT FROM table1 t1
   WHERE  (t1.A,t1.B) = (t2.A,t2.B)
   );

db<>fiddle here

Equivalent and typically cheaper.
And the negation NOT EXISTS is not as treacherous as the discouraged (not completely equivalent) NOT IN. See:

Aside, I would call (A,B) a "composite type" or "row type" rather than a "tuple".

2

Thanks to Peter Vandivier (couldnt flag his comment as the answer)

Yes it is possible:

What have you tried😉 – Peter Vandivier 12 mins ago

2
  • To be fair, I literally just copy-pasted your question onto db<>fiddle, so it feels like a self-answer is appropriate here 🤪 – Peter Vandivier Apr 29 at 8:40
  • Not so important here, but table aliases are a good idea? – Vérace Apr 29 at 10:02

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.