Best Practices - prkirankumar/interview-preparation GitHub Wiki

SQL

1. Use EXISTS instead of IN, when you have large number of result records

   SELECT * FROM employees WHERE employee_id IN
   (SELECT employee_id FROM timesheets WHERE work_date >= DATEADD (day,-,GETDATE()))
   
   in the above statement the subquery generates a list of employee_ids
   
   SELECT * FROM employees e 
   WHERE EXISTS
   (SELECT 1 FROM timesheets t WHERE t.employee_id = e.employee_id AND t.work_Date >= DATEADD(day,-7,GETDATE()))
   
   in the above subquery checks for the presence of a matching records
   

   Note: Never use * to pull all the columns, instead of use specific column names. just for understanding I have used the *