Query records in one table that exists in either of two columns in another table-CodePudding

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:

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)
;