Checking if specific tuple exists in table

Question

Is there a way to check if a specific tuple exists in a table in a where-in statement?

Something like:

create table Test(A int, B int);

insert into Test values (3, 9);
insert into Test values (6, 7);
insert into Test values (7, 6);
insert into Test values (3, 4);

select A, B
from Test
where (B, A) in Test;

Expected output:

6|7
7|6

So, you want to check e.g. whether there is a row where A=6 and B = 7 ? — nos, Nov 06 '11 at 21:46
I want to find all rows that also exist in the table reversed. Although the real question is if you can check if a tuple is in a table rather than just a value in a column. — Acorn, Nov 06 '11 at 21:47
If you don't mind to actually fetch the values from the table, it's even simpler: `select A, B from TEST where (A=6 and B=7) or (B=6 and A=7)`. This will work efficiently on big tables if you have indexes over (A,B) and (B,A), or at least separate indexes for A and B columns. — 9000, Nov 06 '11 at 21:57

score 11 · Answer 1 · edited Nov 09 '18 at 15:22

11

You were super close, the second half of an "in" clause has to be a select... so

SELECT A,B
FROM Test
WHERE (B,A) IN (SELECT B,A FROM Test);

The test (IN) must be in the same fields (or types of fields)

edited Nov 09 '18 at 15:22

Anselmo Blanco Dominguez

309
2
10

answered Nov 06 '11 at 21:51

FrankieTheKneeMan

6,645
2
26
37

I still get the following: `Error: near line 10: near ",": syntax error` – Acorn Nov 06 '11 at 21:54
I presume it's throwing on the second comma? Try ROW(B,A)... (I'm sorry I can't test it myself, I haven't SQLite)... – FrankieTheKneeMan Nov 06 '11 at 22:02

score 3 · Accepted Answer · answered Nov 06 '11 at 21:53

3

Join Test to itself thusly:

select t1.A, t1.B
from Test t1
join Test t2 on t1.A = t2.B and t1.B = t2.A

Or use an intersection:

select A, B from Test
intersect
select B, A from Test

The self-join would probably be faster though.

answered Nov 06 '11 at 21:53

mu is too short

426,620
70
833
800

score 0 · Answer 3 · answered Nov 06 '11 at 22:05

You may get some confused looks when you use the word "tuple" in that way in the context of a database, because the word "tuple" also has a formal definition in database theory that is different from the set theory definition implied in your question.

If you're trying to identify unwanted tuples, you could try this approach:

SELECT t1.A, t1.B From Test t1 JOIN Test t2 ON t1.A=t2.B AND t1.B=t2.A WHERE t1.A > t1.B

Checking if specific tuple exists in table

3 Answers3