Home > Oracle, SQL > SQL Quiz: Which Query is Better?

SQL Quiz: Which Query is Better?


In general, there are lots of ways of expressing a particular query requirement in SQL with implications for query performance. For example, which departments have employees with salaries greater than a certain cutoff? Here are two ways to express this query requirement in SQL. The first uses a conventional correlated subquery while the second uses ANSI join syntax. Which is better? From a theoretical perspective? From a practical perspective? In certain situations? Since I can only pick one, which one should I pick?

VARIABLE salary_cutoff NUMBER

-- Correlated subquery

SELECT d.department_name
FROM departments d
WHERE EXISTS (
  SELECT *
  FROM employees e
  WHERE salary > :salary_cutoff
  AND department_id = d.department_id
);

-- ANSI join syntax

SELECT d.department_name
FROM (
  SELECT DISTINCT department_id
  FROM employees
  WHERE salary > :salary_cutoff
) e
JOIN departments d ON e.department_id = d.department_id;

Inquiring minds want to know!

P.S. The above queries will work unmodified in the HR sample schema.

Categories: Oracle, SQL
  1. September 23, 2009 at 9:30 am

    XE:
    Correlated: 0 sorts (memory) and understandable syntax
    ANSI: 1 sorts (memory)

  2. Gary
    September 23, 2009 at 4:48 pm

    My rule of thumb is, a table should only be in the FROM clause if columns in that table appear in the SELECT clause. Otherwise you are not selecting from it, and its place is in the where clause.

    • September 25, 2009 at 10:23 am

      The best choice depends on the data sizes and distribution patterns, index definitions, and Oracle version.

      Without checking the HR schema, I’d
      have to say that your interviewer might have been correct. If there were an index on salary a very high cutoff value would presumably find very few employees, have to do a very small sort, and then index into a very small number of departments.

  1. September 25, 2009 at 1:18 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: