query with 'NOT IN' condition is too slow

Question

I have a query that reads about 342 records from one table and checks if the records do not exist in the another table with approximately 32000 records. for this, I have used the 'NOT IN' condition and What is ever the best way to run a query faster with 'NOT IN' condition as bellow that seems like the process wants to take whole my life!

SELECT fname,lname,position FROM employees WHERE employees.id NOT IN(select projects.empid where projects.id='BRS213F-013')

What am I really supposed to do?

actually this is not the complete query, there is a FROM missing in the inner select. — scones
– scones, Commented Apr 3, 2013 at 5:48
Yes the from is missing but it is obvious the from clause is project that i missed! :D — deviloper
– deviloper, Commented Apr 3, 2013 at 5:50

John Woo · Accepted Answer · 2013-04-03 05:58:26Z

how about using LEFT JOIN,

SELECT a.fname, a.lname, a.position FROM employees a LEFT JOIN projects b ON a.ID = b.emp_ID AND b.id = 'BRS213F-013' WHERE b.emp_ID IS NULL

Make sure that Employees.ID and projects.emp_ID must have keys defined on them to make the performance faster.

To make Employees.ID a primary key if it has not implemented yet,

ALTER TABLE Employees ADD CONSTRAINT tb_pk PRIMARY KEY (ID)

To make projects.emp_ID a foreign key which reference of Employees.ID if it has not implemented yet

ALTER TABLE Projects ADD CONSTRAINT tb_fk FOREIGN KEY (EmpID) REFERENCES Employees (ID)

NOT IN is usually really slow, and this suggestion is the best chance of lowering the time without any structural work on the database.

Santhosh · Accepted Answer · 2013-04-03 06:17:41Z

SELECT fname,lname,position FROM employees WHERE NOT EXISTS (select * where projects.id='BRS213F-013' AND employees.id = projects.empid)

Gentlezerg · Accepted Answer · 2013-04-03 07:19:51Z

0

NOT EXISTS is more efficient than not in

answered Apr 3, 2013 at 7:19

Gentlezerg

2862 silver badges9 bronze badges

Collectives™ on Stack Overflow

query with 'NOT IN' condition is too slow

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related