Unanswered: Splitting complex SQL select query in to independent sequence of small queries.
Consider I have the following table structures with approx no. of records in each.
#Approx no. of rows in the table: 1 million
#Approx no. of rows in the table: 1 Hundred
Target Result: is to get the employee name who's id is '100' and the department name he belongs to.
Ideal Query : Consider it as COMPLEX QUERY
from employee emp, department dept
emp.dept_id = dept.dept_id
emp.emp_id = '100';
Optimized Independent Queries : Consider it as SIMPLE QUERIES
Now i want to split the above query in to two independent queries.
from employee emp
where emp.id = '100';
:: Returns one row as emp.id is unique.
select dept.dept_id, dept.name
from department dept
:: Returns 100 rows as there are only 100 rows.
Need Your Suggestions Here
If you compare the COMPLEX QUERY against SIMPLE QUERIES, i prefer to use SIMPLE QUERIES, get the results
and compare department id against two results programmatically and then get the final result. I know the database
query engines will do similar query optimization like dividing COMPLEX QUERY to SIMPLE QUERIES and run them
in some specific order to get results faster.
Now is it possible to get the exact sub queries which the database query engine (ANY database query engine) generates
out of complex select query? I mean i don't want to do the splitting manually but i still need those sub-queries.