1

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

Improve this question
0

2 Answers 2

Reset to default
3

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

Improve this answer
1
  • In fact, (a,b,c) is another syntax for ROW(a,b,c) - ROW keyword is optional, docs Commented Jun 13, 2023 at 4:47
3

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

Yes it is possible:

What have you tried😉 – Peter Vandivier 12 mins ago

Improve this answer
2
  • To be fair, I literally just copy-pasted your question onto db<>fiddle, so it feels like a self-answer is appropriate here 🤪 Commented Apr 29, 2021 at 8:40
  • Not so important here, but table aliases are a good idea? Commented 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.