Replace correlated subquery with CTE and JOIN-CodePudding

I am trying to rewrite a query which has a correlated subquery, the idea is to replace it with a CTE and join it later.

I have three tables, tbl_transaction, tbl_beneficiaries and tbl_reg_countries. The current (in short) SQL looks like the following.

SELECT
   t.USER_ID,
   t.TRANSACTION
FROM tbl_transactions t
JOIN tbl_beneficiaries b ON b.ID = t.USER_ID
WHERE b.COUNTRY NOT IN (
   SELECT rc.country
   FROM tbl_reg_countries rc
   WHERE rc.id = t.USER.ID)

My goal is to query only those transactions for each user where the transaction happens outside of the registered countries. So a user may registered X,Y,Z country but had business with Q. In that case only Q should be returned. How could this be replaced with a CTE/JOIN?

CodePudding user response：

I assume both tbl_beneficiaries.COUNTRY and tbl_reg_countries.COUNTRY are not nullable. You can use a LEFT JOIN with NULL test to detect never matching rows

SELECT
   t.USER_ID,
   t.TRANSACTION
FROM tbl_transactions t
JOIN tbl_beneficiaries b ON b.ID = t.USER_ID
LEFT JOIN tbl_reg_countries rc ON rc.id = t.USER_ID AND b.COUNTRY = rc.country
WHERE rc.country IS NULL

CodePudding user response：

I would try rewriting query with "with"

Like this:

With a As
(Select 
    Distinct rc.country
From tbl_reg_countries rc
Inner Join tbl_transactions t on rc.id = t.USER.ID
)

Select 
   t.USER_ID,
   t.TRANSACTION
From tbl_transactions t
Inner Join tbl_beneficiaries b On b.ID = t.USER_ID
Where b.COUNTRY Not In (select * from a)