Any way to faster sql query containing IN clause where data for IN clause is obtained from with in a query?

Question

During my project, I came across a requirement where I need to process some operation over some address which is not present inside some other table, and for that, I have written the following query. But I think this query will go slow when the entries in the second table 'transaction' increases.

select emp_address
from emp_leave_wallet
where attached  ="mycompany_wallet" and
      emp_address not in (select destination_address from transaction);

Any other way, other than adding Index over destination_address.

I think right or left join is the better alternative. But it is hard to say without knowing your table stuctrue — Jens, Aug 21 '18 at 12:13
Your query is fine. It can be re-written with `NOT EXISTS` or an anti join. However, the optimizer should know this too, and get to the same execution plan for all three queries. If `destination_address` is nullable however, you must be careful with `NOT IN`, as this will not return any row, if there is a null entry in `transaction` (because null is supposed to be an unknown value, so the DBMS cannot honestly tell you the `emp_address` is not in the set, or so they argue ;-) — Thorsten Kettner, Aug 21 '18 at 12:35
Also MySQL tends to optimize worse with a large number of records going into the `IN` or `NOT IN` operators @ThorstenKettner so this query is likely a good candidate to be rewritten into a `LEFT JOIN` instead — Raymond Nijland, Aug 21 '18 at 12:39

score 2 · Answer 1 · answered Aug 21 '18 at 12:40

A solution using a JOIN but I can't quantify the performance gain:

SELECT ew.emp_address
FROM emp_leave_wallet ew
LEFT OUTER JOIN transaction t on
    t.emp_address = ew.emp_address
WHERE ew.attached = "mycompany_wallet" and
      t.emp_address IS NULL

score 1 · Answer 2 · answered Aug 21 '18 at 12:14

I would start with not exists:

select lw.emp_address
from emp_leave_wallet lw
where lw.attached  = 'mycompany_wallet' and
      not exists (select 1
                  from destination_address da
                  where lw.emp_address = da.destination_address 
                 );

Then for this query you definitely want an index on destination_address(destination_address) and probably on (attached, emp_address).

score 1 · Answer 3 · answered Aug 21 '18 at 12:15

1

Use not exists :

select ew.emp_addres
from emp_leave_wallet ew
where ew.attached = "mycompany_wallet" and
      not exists (select 1 from transaction t where t.destination_address = ew.emp_address);

answered Aug 21 '18 at 12:15

Yogesh Sharma

49,870
5
26
52

Kamil Gosciminski · Answer 4 · 2018-08-21T12:17:15.433

1

Use NOT EXISTS which returns record if there is no matching row in transaction table based on where condition:

select emp_address
from emp_leave_wallet e
where attached = 'mycompany_wallter'
  and not exists (
    select 1
    from transaction t
    where e.emp_address = t.destination_address
    )

Create indexes:

CREATE INDEX idx1 ON emp_leave_wallet(attached, emp_address);
CREATE INDEX idx2 ON transaction(destination_address);

edited Aug 21 '18 at 12:17

answered Aug 21 '18 at 12:15

Kamil Gosciminski

16,547
8
49
72

How not exists will save the day and will be able to make query even faster, could you please explain. thank you. – Thinker Aug 21 '18 at 12:16
Google: NOT IN vs NOT EXISTS. Many resources there. An example on SO would be: https://stackoverflow.com/questions/173041/not-in-vs-not-exists Another blog: http://www.sqlservercentral.com/blogs/sqlinthewild/2010/02/18/not-exists-vs-not-in/ – Kamil Gosciminski Aug 21 '18 at 12:18

score 0 · Answer 5 · edited Aug 21 '18 at 12:51

0

select emp_address
from emp_leave_wallet
where attached  ="mycompany_wallet" and
      emp_address not in (select destination_address from transaction);

Your emp_leave_wallet and transaction table should have id fields (I'm guessing ... emp_id, transaction_id)

If it were me i would...

select emp_address
from emp_leave_wallet elw
inner join transaction t
on elw.emp_id = t.transacation_id

edited Aug 21 '18 at 12:51

sticky bit

36,626
12
31
42

answered Aug 21 '18 at 12:45

Will

228
1
2
15

Any way to faster sql query containing IN clause where data for IN clause is obtained from with in a query?

5 Answers5