More on explain, particularly in the context of joins, possibly with where-conditions.
Use the bigcompany database here.
1. From http://pld.cs.luc.edu/database/sql2.html#nested:
select e.fname, e.lname, e.salary from
employee e
where e.salary >= 0.7* (select e1.salary from employee e1 where e1.ssn
= e.super_ssn);
select e.fname, e.lname, e.salary from
employee e join employee e1 on e.super_ssn = e1.ssn
where e.salary > 0.7*e1.salary;
Compare explain output and total time. There is a tenfold difference!
2. From http://pld.cs.luc.edu/database/sql2.html#nested2:
select e.fname, e.lname from employee e
where e.ssn in
(select d.essn from dependent d where e.fname = d.dependent_name and e.sex
= d.sex);
select e.fname, e.lname from employee e join
dependent d on e.ssn = d.essn
where e.fname = d.dependent_name and e.sex = d.sex;
Again, compare explain output and total time. Here the time difference is smaller; why? Are the explain plans similar?
3. From http://pld.cs.luc.edu/database/sql2.html#exists:
select e.fname, e.lname from EMPLOYEE e
where exists
(select * from DEPENDENT d where e.ssn = d.essn and e.fname =
d.dependent_name and e.sex = d.sex);
Here we're back to a significant time difference.
4. From http://pld.cs.luc.edu/database/sql2.html#except:
select distinct e.lname, e.ssn, e.dno from
employee e left join employee2 e2 on e.ssn = e2.ssn
where e2.ssn is null;
select distinct e.lname, e.ssn, e.dno from
employee e
except
select distinct e.lname, e.ssn, e.dno from employee2 e
What is the difference in explain output?