Exclusion Query Notes (employees)

gistfile1.sql

$ (for x in {1..100}; do echo 'select * from departments d where (select count(*) from dept_emp de where d.dept_no = de.dept_no) = 0;'; echo; done ) | ./mk-query-profiler  -u root --database=employees 

+----------------------------------------------------------+
|                     100 (0.1104 sec)                     |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                       11.596
   Questions                               100
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                                100
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                      10600
   Data out of server                    18545
   Optimizer cost                            0.000

__ Table and index accesses ____________ Value _____________
   Table locks acquired                    200
   Table scans                             100
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0


$ (for x in {1..100}; do echo 'select * from departments d left join dept_emp de using (dept_no) where  de.dept_no is null;'; echo; done ) | ./mk-query-profiler  -u root --database=employees 

+----------------------------------------------------------+
|                     100 (0.0003 sec)                     |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                        0.027
   Questions                               100
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                                100
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                       9600
   Data out of server                    36787
   Optimizer cost                     10518299.900

__ Table and index accesses ____________ Value _____________
   Table locks acquired                    200
   Table scans                             100
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0


$ (for x in {1..100}; do echo 'select * from departments d where d.dept_no NOT IN (select dept_no from dept_emp de) ;'; echo; done ) | ./mk-query-profiler  -u root --database=employees 

+----------------------------------------------------------+
|                     100 (0.0002 sec)                     |
+----------------------------------------------------------+

__ Overall stats _______________________ Value _____________
   Total elapsed time                        0.019
   Questions                               100
     COMMIT                                  0
     DELETE                                  0
     DELETE MULTI                            0
     INSERT                                  0
     INSERT SELECT                           0
     REPLACE                                 0
     REPLACE SELECT                          0
     SELECT                                100
     UPDATE                                  0
     UPDATE MULTI                            0
   Data into server                       9000
   Data out of server                    18031
   Optimizer cost                            0.000

__ Table and index accesses ____________ Value _____________
   Table locks acquired                    200
   Table scans                             100
     Join                                    0
   Index range scans                         0
     Join without check                      0
     Join with check                         0
   Rows sorted                               0
     Range sorts                             0
     Merge passes                            0
     Table scans                             0
     Potential filesorts                     0