News for hackers. Stuff that matters.
Re:Learn SQL, dammit by pjungwir on Wednesday May 08, 2013 @02:45PM (#5676055)
A class of query I love that scares off a lot of developers is a correlated sub-query, where the subquery references a value from the outer query. For example, finding all employees with at least one assignment:

    SELECT *
    FROM   employees e
    WHERE  EXISTS (SELECT 1
                   FROM   assignments a
                   WHERE  a.employee_id = e.id)
For a while in Oracle this was a lot faster than IN/NOT IN. I'm not sure if that's still the case, or if it's true for other systems. I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN.

EDIT: This kind of query is great with Rails scopes, because you can write something like this:

    class Employee
      scope :with_assignments, where(<<-EOQ)
        EXISTS (SELECT 1
                FROM   assignments a
                WHERE  a.employee_id = employees.id)
      EOQ
    end
and that is easily composeable with other scopes/conditions/etc since it doesn't force you to use any joins. Yay for mixing SQL with your ORM!
Re:Learn SQL, dammit by itsybitsycoder on Wednesday May 08, 2013 @02:59PM (#5676132)
I believe I read that in Postgres the query planner does the same thing whether you use EXISTS/NOT EXISTS or IN/NOT IN.

I don't think this is true, unless it's a very recent change. Here's a post from 2009 comparing NOT IN/NOT EXISTS/LEFT JOIN WHERE IS NULL for Postgres:

http://explainextended.com/2009/09/16/not-in-vs-not-exists-v...

Re:Learn SQL, dammit by pjungwir on Wednesday May 08, 2013 @03:45PM (#5676410)
In the NOT case, NOT IN and NOT EXISTS may even return different results! I think I knew that once and had forgotten. Good to be reminded!
Re:Learn SQL, dammit by obviouslygreen on Wednesday May 08, 2013 @02:48PM (#5676069)
I recently discovered that MySQL before 5.6 (5.5 being the latest available on RDS, of course) does not honor indexes if they should be invoked for a subquery, e.g.

select whatever from wherever where user_id in (select id from users where somethingorother like '%lol%');

Got an index on user_id? Too bad. Ignored.

If you precompute the values, though?

select whatever from wherever where user_id in (1, 2, 3);

Sweet, I love indexes! I'll definitely use them.

Re:Learn SQL, dammit by michaelt on Wednesday May 08, 2013 @04:08PM (#5676548)
Let's say your inner query returns n rows and wherever contains m rows. And your index is a B-tree, so you can hit it once in O(log m).

If you use a nested loop join, where you hit the index once for each inner query result, that's O(n*log m). If on the other hand you do a hash join, skipping the index but doing a full table scan of wherever, the complexity is O(m+n). So which is the faster choice depends on how many rows the inner query returns.

If you want to plan up front, before you know what m is, how would you decide which join to use?

Re:Learn SQL, dammit by miahi on Wednesday May 08, 2013 @04:03PM (#5676509)
Loks like it cannot evaluate the subquery result (number of items).
Re:Learn SQL, dammit by chris_mahan on Wednesday May 08, 2013 @03:14PM (#5676221)
You can use CROSS APPLY on tsql, and it allows you to use an order by in the inner query, for example, if you wanted to know the id of the employee's latest assignment. SELECT e.* , assignment_id = b.id FROM employees e CROSS APPLY (SELECT top 1 a.id FROM assignments a WHERE a.employee_id = e.id ORDER BY a.assignment_date DESC ) b

In tsql, you are not allowed to have order by in a subquery (if memory serves me right)

Edit: Oh crap, I forgot this isn't SO, and the formatting went out the window.

Re:Learn SQL, dammit by pfg on Wednesday May 08, 2013 @04:30PM (#5676711)
You can use ORDER BY in connection with TOP n, for example if you want to use the result of a subquery as a column:

  SELECT
    user_name,
    (SELECT TOP 1 last_action FROM actions WHERE actions.user_id=users.user_id ORDER BY action_timestamp)
  FROM
    users
(TOP is T-SQLs version of LIMIT)

Also, thanks for making me aware of CROSS APPLY, haven't seen that before!

Re:Learn SQL, dammit by chris_mahan on Wednesday May 08, 2013 @06:00PM (#5677235)
Sure. Note also that CROSS APPLY allows you to retrieve multiple columns, unlike an in-SELECT subquery (unless you want to have that query written multiple times)
Re:Learn SQL, dammit by daigoba66 on Wednesday May 08, 2013 @03:36PM (#5676353)
Depending on the query and the optimizer, the resulting plan for IN/NOT IN might be the same as EXISTS/NOT EXISTS. E.g. a left semi join. I know this is true for MSSQL Server, I can't speak for Oracle.
Re:Learn SQL, dammit by yxhuvud on Wednesday May 08, 2013 @03:09PM (#5676195)
Why would you ever want to use such a horrible statement instead of using an inner join?

scope :with_assignments, joins(:assignments)

(or :assignment, depending on how your association is defined)

Re:Learn SQL, dammit by pjungwir on Wednesday May 08, 2013 @03:15PM (#5676231)
Well, adding joins to the outermost query can limit your options re how you use the scope. For instance, `joins` sets a read-only flag on the AR result, so you can't say Employee.with_assignments.destroy_all, for example, to, um, fire all your busy people. :-)
Re:Learn SQL, dammit by DrJokepu on Wednesday May 08, 2013 @03:24PM (#5676276)
OP's query is not the same as an inner join; it will only return each employee only once while your query will return each employee for every assignment it is associated with.
Re:Learn SQL, dammit by msluyter on Wednesday May 08, 2013 @03:23PM (#5676267)
I think IN is equally fast in Oracle, or at least it was when I was working with 10.2.

In Postgres, it appears that this does the same thing as IN.

Re:Learn SQL, dammit by pjungwir on Wednesday May 08, 2013 @03:40PM (#5676376)
I haven't used Oracle since 8, and the only reference I can find now from those days are a couple AskTom articles [0] [1]. They are quite worth reading, although what they say re performance is not quite what I (mis?)remember.

[0] http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QU...

[1] http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUEST...

Re:Learn SQL, dammit by nimish on Wednesday May 08, 2013 @03:11PM (#5676206)
This is a textbook semijoin, but you can see what the postgres planner does with explain and explain analyze tacked in front of a query.