Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though.
Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server.
It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow available memory (similar to a table spill in SQL Server). Otherwise, it'll do a pair of index scans (you do have indexes on
symbol in both tables, right?), then a join and output, all in memory.
- If the indexes (indices?) aren't selective enough, it will default to a table scan instead. I'm not sure what "enough" is, but I think it's around 30% (i.e. if statistics tell the optimiser it'll get more than 30% of the rows in the table, then just scanning straight through the table will cost the optimiser less than index scanning+lookups).
- Worktables also can come into play if you have an
ORDER BY statement or if the optimiser decides to use a merge join and the join criterion isn't the primary key (since a merge join requires sorted inputs).
- Also, the cost estimation at the bottom of any showplan for any non-trivial plan is meaningless, it's a dimensionless value (i.e. just a number) meant for comparisons to other plans for that query only. i.e. You can't compare two queries on two different data sets (even on the same server) and say one's more expensive than the other, because the cost estimation isn't comparable like that. There's also some cases I've seen where the cost estimation for the same query has been wildly off (e.g. merge join with a cost of 1000 takes several hours to complete,
SET MERGE_JOIN OFF and it defaults to NL joins with a cost of 10000, but runs in minutes). Optimising Sybase's query processor is a whole different book (actually there's four books, I just linked the advanced QP one).
- There are some tiny corner cases where the query processor will do things in an obviously suboptimal way, but in 99% of cases you won't run into those.
That said, based on what I've seen the query plan would look something like this: (this is vastly simplified, the actual output from sp_showplan is rather more detailed)
|ROOT:EMIT (aka select)
| | NL JOIN (nested loop join)
| | | RESTRICT Table X
| | |
| | | | SCAN Table X (using index, forward scan)
| | | RESTRICT Table Y
| | |
| | | | SCAN Table Y (using index, forward scan)
The reason it shows the
RESTRICT after the
SCAN is because without the
SCAN there's no data to
RESTRICT - I'm reasonably sure that you can effectively consider them as one operation in this case (i.e. the restriction conditions will be part of each index scan, rather than the index scan copying a whole lump of data and restrict throwing most of it out).