Search code examples
sqlsqlitetupleswhere-in

Checking if specific tuple exists in table

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
Solution

  • 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.