Using tuples in SQL in clause

Question

Given a database like this:

BEGIN TRANSACTION;

CREATE TABLE aTable (
    a STRING,
    b STRING
);

INSERT INTO aTable VALUES('one','two');
INSERT INTO aTable VALUES('one','three');

CREATE TABLE anotherTable (
    a STRING,
    b STRING
);

INSERT INTO anotherTable VALUES('one','three');
INSERT INTO anotherTable VALUES('two','three');

COMMIT;

I would like to do something along the lines of

SELECT a,b FROM aTable
WHERE (aTable.a,aTable.b) IN
(SELECT anotherTable.a,anotherTable.b FROM anotherTable);

To get the answer 'one','three', but I'm getting "near ",": syntax error"

Is this possible in any flavour of SQL? (I'm using SQLite)

Am I making a gross conceptual error? Or what?

Michael Buen · Accepted Answer · 2009-09-25T01:36:32.843

22

your code works if you do it in PostgreSQL or Oracle. on MS SQL, it is not supported

use this:

SELECT a,b FROM aTable
WHERE 
-- (aTable.a,aTable.b) IN -- leave this commented, it makes the intent more clear
EXISTS
(
    SELECT anotherTable.a,anotherTable.b -- do not remove this too, perfectly fine for self-documenting code, i.e.. tuple presence testing
    FROM anotherTable
    WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);

[EDIT]

sans the stating of intent:

SELECT a,b FROM aTable
WHERE     
EXISTS
(
    SELECT *
    FROM anotherTable
    WHERE anotherTable.a = aTable.a AND anotherTable.b = aTable.b
);

it's somewhat lame, for more than a decade, MS SQL still don't have first-class support for tuples. IN tuple construct is way more readable than its analogous EXISTS construct. btw, JOIN also works (tster's code), but if you need something more flexible and future-proof, use EXISTS.

[EDIT]

speaking of SQLite, i'm dabbling with it recently. yeah, IN tuples doesn't work

edited Sep 25 '09 at 01:36

answered Sep 25 '09 at 01:27

Michael Buen

38,643
9
94
118

definitive answer in 10 mins! doesn't work in sqlite :-( voted and accepted – mr calendar Sep 25 '09 at 01:32
1

Even if it did work in sqlite, I would recommend not doing it. This is bound to confuse future maintainers of your code. (NOTE: you are the future maintainer, so it's you who will be confused) – tster Sep 25 '09 at 01:37
4

@tster - I fail to see the source of any confusion – mr calendar Nov 01 '09 at 23:37
1

*"it's somewhat lame, for more than a decade, MS SQL still don't have first-class support for tuples. IN tuple construct is way more readable than its analogous EXISTS construct."* Yep, I landed here because of such limitation from MS SQL. For all people who cares, please vote for it here -- **Add support for ANSI standard row value constructors** https://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors – xpt Oct 30 '14 at 15:43

score 2 · Answer 2 · answered Sep 25 '09 at 01:16

2

you can use a join:

SELECT aTable.a, aTable.b FROM aTable
JOIN anotherTable ON aTable.a = anotherTable.a AND aTable.b = anotherTable.b

answered Sep 25 '09 at 01:16

tster

17,883
5
53
72

This works. Plus the answer showed up in 5 minutes! Still interested to know whether my proposed code is possible / hopelessly misconceived / whatever? – mr calendar Sep 25 '09 at 01:24
1

Yes, you are trying to write in a way that you understand, but this problem is solved much more naturally using set theory, the basis of SQL. A very common usage of SQL is to join two tables to get the intersection of them, which is what you are trying to do. – D'Arcy Rittich Sep 25 '09 at 01:31
@OrbMan: IN is more intuitive; JOIN at times, is too much computer-sciencey-looking. And it's just a matter of having more first-class construct from a language to be able to accomplish something easier(think C#'s properties vs Java setter/getter). If the IN works for single value, i think you'll be more than glad too if the RDBMS you are using also works on paired values(tuples) – Michael Buen Sep 25 '09 at 01:49
1

JOIN can have a problem if anotherTable has duplicate values, EXISTS is more flexible/future-proof than its JOIN cousin: http://stackoverflow.com/questions/1001543/in-vs-join-with-large-rowsets/1001578#1001578 – Michael Buen Sep 25 '09 at 01:55
1

this article explain set testings(IN vs JOIN vs EXITS) very nicely, including the logic and execution plan on non-unique values, and non-indexed values: http://explainextended.com/2009/06/16/in-vs-join-vs-exists/ – Michael Buen Sep 25 '09 at 02:04

Snorex · Answer 3 · 2015-03-13T13:33:01.537

1

Another alternative is to use concatenation to make your 2-tuple into a single field :

SELECT a,b FROM aTable
WHERE (aTable.a||'-'||aTable.b) IN
(SELECT (anotherTable.a || '-' || anotherTable.b FROM anotherTable);

...just be aware that bad things can happen if a or b contain the delimiter '-'

edited Mar 13 '15 at 13:33

answered Mar 12 '15 at 18:29

Snorex

904
12
29

1

This solution would have an other disadvantage: the query optimizer cannot reason about such expressions and so cannot optimize the query. – iuzuz Jan 23 '18 at 14:59

Using tuples in SQL in clause

3 Answers3

Linked