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...
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.
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?
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.
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!
scope :with_assignments, joins(:assignments)
(or :assignment, depending on how your association is defined)
In Postgres, it appears that this does the same thing as IN.
[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...
EDIT: This kind of query is great with Rails scopes, because you can write something like this:
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!