Home > Software design >  Query records in one table that exists in either of two columns in another table
Query records in one table that exists in either of two columns in another table

Time:06-15

I have two tables. One with user info, one with payment info. I would like to find out users that are either the sender or the receiver of a payment.

Eample data:

user

id other columns
1
2
3

payments:

sender receiver other columns
1 4
1 3
5 3
4 5

ideal output

id
1
3

what I tried:

SELECT id
FROM user u
where exists
(
SELECT 1
FROM payments p
where u.id = p.sender or u.id = p.receiver
)

BigQuery gave the error:

LEFT SEMI JOIN cannot be used without a condition that is an equality of fields from both sides of the join

which is quite confusing to me

CodePudding user response:

It's because WHERE u.id = p.sender or u.id = p.receiver makes LEFT SEMI JOIN to be non-equi join.

You can separate WHERE condition into 2 EXITS clauses.

SELECT id 
  FROM user u
 WHERE EXISTS (SELECT 1 FROM payments p WHERE u.id = p.sender) 
    OR EXISTS (SELECT 1 FROM payments p WHERE u.id = p.receiver)
;

output:

enter image description here

But this approach sometimes shows very poor performance in real circumstances.

So, below query would be another option you can use in that case.

SELECT id FROM user u WHERE EXISTS (SELECT 1 FROM payments p WHERE u.id = p.sender) 
 UNION ALL
SELECT id FROM user u WHERE EXISTS (SELECT 1 FROM payments p WHERE u.id = p.receiver)
;
  • Related