CODEWITHSUNDEEP
mysqlsql
biztiger
biztiger

Reputation: 1487

Sql Query Optimization - Remove Not In Operator

I am storing employee attendance on a table 'attendance' having the following structure:

EmpID, CDate

Attendance system insert this table everyday with employee-id of all employees present on that particular day.

I need to find out absent statement of a particular employee. I can do this easily by selecting all distinct date that are not in - dates where the employee is present.

Is there any way I can remove the not in operator on that sql statement. Please help

Here is the sql query for employee with EmpId 01:

select distinct CDate 
  from attendance 
  where CDate not in (
      Select CDate from attendance where EmpID='01')

Upvotes: 1

Views: 520

Answers (2)

John Green
John Green

Reputation: 13445

The problem isn't in the NOT IN clause, it is the subquery.

You want something more like:

SELECT DISTINCT a1.CDate, if (EmpID=NULL, false,true) as Present
FROM attendance as a1
LEFT JOIN attendance as a2 USING (CDate) 
WHERE a2.EmpID='01'

This is a cartesian join which pulls all of the dates, then joins the employee attendance status on that. Should be significantly faster than your subquery.

Updated, with tested code:

SELECT DISTINCT a1.CDate, IF (a2.EmpID IS NULL, false,true) as Present
FROM attendance AS a1
LEFT JOIN attendance AS a2 ON (a1.CDate = a2.CDate AND a2.EmpID='01')

My bad on the previous answer. I should have put the subselection into the ON instead of an aggregate.

Upvotes: 1

Łukasz Rysiak
Łukasz Rysiak

Reputation: 3078

You could change your mechanism to store data for each employee, each day. Yes, it'll add a lot of rows, but how can you be sure that you'll get all dates from logged data? what if theres nobody at work today? no one will have absense?

If you'd go with:

EmpID, CDate, Present
1, {date}, 0|1

then you'd have simpler and faster query traded for table size:

select CDate from attendance where EmpID = 1 and status = 0;

Upvotes: 1

Related Questions