Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 671
mr calendar Avatar asked Sep 25 '09 01:09

mr calendar


People also ask

Which clause in SQL is used to specify the number of tuples present in a database table?

The WHERE clause specifies a condition for rows (or row combinations) in these tables that are considered in this query (absence of C is equivalent to C ≡ TRUE). The SELECT clause specifies the attributes of the result tuples (* ≡ output all attributes occurring in R1,...,Rm).

In which clause of SQL tuple variables are defined?

Tuple variables can be used in SQL, and are defined in the from clause: select distinct cname, T.loan# from borrower as S, loan as T.

Which clause is used to sort the tuples in a table?

Answer: ORDER BY Clause is used to display the tuples in ascending order of an attribute.


2 Answers

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

like image 118
Michael Buen Avatar answered Oct 05 '22 22:10

Michael Buen


you can use a join:

SELECT aTable.a, aTable.b FROM aTable
JOIN anotherTable ON aTable.a = anotherTable.a AND aTable.b = anotherTable.b
like image 26
tster Avatar answered Oct 05 '22 21:10

tster