CODEWITHSUNDEEP
sqlrow-value-expression
mr calendar
mr calendar

Reputation: 995

Using tuples in SQL in clause

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?

Upvotes: 12

Views: 28405

Answers (3)

Snorex
Snorex

Reputation: 914

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 '-'

Upvotes: 1

Michael Buen
Michael Buen

Reputation: 39423

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

Upvotes: 22

tster
tster

Reputation: 18237

you can use a join:

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

Upvotes: 2

Related Questions