omri_saadon omri_saadon - 10 months ago 44
SQL Question

SQL - using the same table in the outer query and in the sub query

I'm trying to understand the logic of the simple following query:

select *
from table1 as t1 cross join table2 as t2
where t2.source_code = (
select Max(t3.source_code)
from table2 t3
where t3.source_code in (t1.code_value)

I've read about sub queries in some tutorials, also iv'e been searching for a related question as mine, i haven't found a clear answer.

My question is how can i know the complexity of this query (how many times the sub-query runs).

I'm not quite sure how the sub query works here as i use a table from the outer query inside the sub query.

Answer Source

This (Terrible, Horrible, No Good, Very Bad) query is equivalent to:

select * from table1 as t1 join table2 as t2 on t1.code_value = t2.source_code;

If the relation between tables is 1:1 or 1:N then the theoretical complexity is O(n).
The actual complexity is database/data structures/statistics dependent.