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?

Irfan

Answer 1

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

Disciple

definitive answer in 10 mins! doesn't work in sqlite :-( voted and accepted – Irfan

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) – Middlebreaker

@Middlebreaker - I fail to see the source of any confusion – Irfan

"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 connect.microsoft.com/SQLServer/feedback/details/299231/… – Forfar

Answer 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

Middlebreaker

This works. Plus the answer showed up in 5 minutes! Still interested to know whether my proposed code is possible / hopelessly misconceived / whatever? – Irfan

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. – Caucasoid

@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) – Disciple

JOIN can have a problem if anotherTable has duplicate values, EXISTS is more flexible/future-proof than its JOIN cousin: #1002043 – Disciple

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: explainextended.com/2009/06/16/in-vs-join-vs-exists – Disciple

Answer 3

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

Inca

This solution would have an other disadvantage: the query optimizer cannot reason about such expressions and so cannot optimize the query. – Dorelle