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 Answers 2

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".

1
  • In fact, (a,b,c) is another syntax for ROW(a,b,c) - ROW keyword is optional, docs
    – Eugene
    Jun 13, 2023 at 4:47
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 🤪 Apr 29, 2021 at 8:40
  • Not so important here, but table aliases are a good idea?
    – Vérace
    Apr 29, 2021 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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