Nested SQL query not working referring two different databases

Question

Query:

SELECT xid 
FROM db2.pat_info 
WHERE xid IN (SELECT DISTINCT xid 
              FROM db1.audit 
              WHERE FUNCTION IN ('ABC','PQR') 
              AND xid NOT LIKE 'test%' 
              AND status = 1 
              AND ques_responded = 9) 
AND fname IS NOT NULL 
AND t_id IN (11,12)

I am a beginner to SQL and facing an issue in executing the query mentioned above. The inner query is executing as expected but when integrated with outer query then I am not getting any result to the query.

If both the queries are executed individually then getting the result as expected.

The 'FUNCTION' column mentioned in inner query is 'function' in the table. It can't be changed as it's old implementation.

Any leads would be helpful.

Answer 1

IN can be quite inefficient. Switch to EXISTS or move to the FROM clause:

SELECT pi.xid 
FROM db2.pat_info pi JOIN
     (SELECT DISTINCT xid 
      FROM db1.audit 
      WHERE FUNCTION IN ('ABC', 'PQR') AND
            xid NOT LIKE 'test%' AND
            status = 1 AND
            ques_responded = 9
     )  a
     ON pi.xid = a.xid
WHERE pi.fname IS NOT NULL AND pi.t_id IN (11, 12);

In some versions of MySQL, the subquery is re-run for every row in the outer table.