5 ms·
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,
by pjungwir 13y ago
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!
- obviouslygreen 13y agoI 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.
- miahi 13y agoLoks like it cannot evaluate the subquery result (number of items).
- michaelt 13y agoLet'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?
- itsybitsycoder 13y agoI 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-vs-left-join-is-null-postgresql/ http://explainextended.com/2009/09/16/not-in-vs-not-exists-v...
- pjungwir 13y agoIn 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!
- yxhuvud 13y agoWhy 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)
- pjungwir 13y agoWell, 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. :-)
- DrJokepu 13y agoOP'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.
- nimish 13y agoThis is a textbook semijoin, but you can see what the postgres planner does with explain and explain analyze tacked in front of a query.
- chris_mahan 13y agoYou 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.
- pfg 13y agoYou 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!
- chris_mahan 13y agoSure. 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)
- msluyter 13y agoI 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.
- pjungwir 13y agoI 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_QUESTION_ID:442029737684 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_QUESTION_ID:953229842074 http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUEST...
- daigoba66 13y agoDepending 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.