I was asked this question at web developer interview. after my answer interviewer said your in second table :(
I have two tables
employee (empid int pk, name varchar(20)`)
badempid int pk, name varchar(20))
Now, I want to select only good employees.
My answer was :
SELECT * FROM employee WHERE empid NOT IN (SELECT badempid from bademployee)
He said this query is not good for performance.
Can any one tell me how to write query for same result, by not using negative terms(not in, !=).
Can it be done using
LEFT OUTER JOIN ?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX bademployee_idx_badempid ON bademployee (badempid);
SELECT * FROM employee WHERE NOT EXISTS ( SELECT 1 FROM bademployee WHERE ( employee.empid = bademployee.badempid ) )