SQL Mini-challenge

As published in the August 2014 issue of the NoCOUG Journal

Note: If you would like to take part in the challenge, please send an email request to sqlchallenge@nocoug.org and we will make you a guest contributor to the NoCOUG blog so that you can post your entry.

The inventor of the relational model, Dr. Edgar Codd, was of the opinion that “[r]equesting data by its properties is far more natural than devising a particular algorithm or sequence of operations for its retrieval. Thus, a calculus-oriented language provides a good target language for a more user-oriented source language” (Relational Completeness of Data Base Sublanguages). Therefore, with the exception of the Union operation, the original version of SQL was based on relational calculus, although, over time, other elements of relational algebra like difference (minus), intersection, and outer join crept in.

Testing of existence in a set using subqueries is the hallmark of relational calculus. The following example has nested subqueries. It lists the locations containing a department that either contains an employee named Steven King or an employee who holds the title of President or or employee who has previously held the title of President.

SELECT l.location_id, l.city
FROM locations l
WHERE EXISTS (
  SELECT * FROM departments d, employees e, jobs j
  WHERE d.location_id = l.location_id
  AND e.department_id = d.department_id
  AND j.job_id = e.job_id
  AND (
    (e.first_name = 'Steven' AND e.last_name = 'King')
    OR j.job_title  = 'President'
    OR EXISTS (
      SELECT * FROM job_history jh, jobs j2
      WHERE jh.employee_id = e.employee_id
      AND j2.job_id = jh.job_id
      AND j2.job_title = 'President'
    )
  )
);

The challenge is to rewrite the above query without subqueries and in the most efficient way possible, as measured by the number of consistent gets; that is, the Buffers column in the query execution plan. Answers must be posted on the NoCOUG blog https://nocoug.wordpress.com. Answers must be tested in the HR sample schema to prove that they actually work and must be accompanied by a query execution plan showing the number of consistent gets used by the rewritten query. The winner will receive a $75 Amazon gift certificate or a prize of equal value. The contest will close when a sufficient number of entries have been received. NoCOUG’s decisions are final. ▲

Update (August 13, 2014): There was a little ambiguity in the problem statement. To avoid ambiguity, the wording should have been “the challenge is to rewrite the above query without testing of existence in a set using subqueries” because the preamble indicated that the goal was to avoid existence testing using techniques such as EXISTS, IN, ALL, ANY, ALL, and SOME. Inline views are therefore permissible. Anything not explicitly disallowed by the problem statement is also permissible.