Nested SELECT vs JOIN?

[ss1289]

What's the performance difference between these two? In other words, what happens at the lower level language when each of these are executed?

Thanks in advance.

[mattyblah]

please give an example so we know exactly what you're asking...

[chazzy]

in general, the inner join will out perform the combined select in the following situation:


select columns from table_a a, table_b b where b.a_id = a.id;




select columns from table_a a inner join table_b b on b.a_id = a.id;

The reason is that the JOIN ... ON syntax filters the results to match the criteria, where as the a,b syntax in the first query does a Cartesian product

[mattyblah]

seems to me like he's trying to figure out the difference between:

select * from sometable where somecolumn in (select somecolumn from someothertable)

vs

select * from sometable st left join someothertable sot on st.somecolumn = sot.somecolumn

In order to truly understand what you're asking, what are you trying to accomplish and what is the sql you're currently using.

[ss1289]

seems to me like he's trying to figure out the difference between:

select * from sometable where somecolumn in (select somecolumn from someothertable)

vs

select * from sometable st left join someothertable sot on st.somecolumn = sot.somecolumn

In order to truly understand what you're asking, what are you trying to accomplish and what is the sql you're currently using.
Basically I want to know more about how each work, like what chazzy said at the end of his comment. I don't really have a specific example or anything. I just wanted to know how each are performed and the difference in how they execute.

[chazzy]

well, if you want to compare my previous two examples with this one:


select columns from some_table where some_value is in (select some_value from something where something_else)

In a lot of ways, it's like comparing apples and oranges. in an obvious sense, the nested select only executes once. it has the significant disadvantage that you can only have one column in the subquery and that column has to be related somehow - meaning you can't expand the output columns based on the subselect.