We Heart Code

Friday, March 16th, 2007...9:42 am

Accessing main query aliases from subquery

I am stupid, I never fully understood Oracle Subqueries. Let me give you an example, I had a really big view where I wanted to access stats of child tables based on a parent table.

For example, let's call that table checks, I wanted to know how many responses were linked to each check. We have a a one to many relationship between checks and responses.

So I wrote my original query something like this:

SELECT c.check_id,cr.count_of_responses
FROM c.checks
WHERE
LEFT JOIN (SELECT count(1),r.check_id AS count_of_responses FROM responses r GROUP BY r.check_id) cr
ON cr.check_id = c.check_id

This works BUT, it produces a full tablescan on the child table unless you specify a check_id in the main query. This aint that useful if you want to say query on the count_of_responses for all checks. What if we could access the main query variables in the subquery? Then the database wouldn't have to full tablescan and join it all together.

Well you can if you do your subquery in the SELECT clause, the only catch is you can only select one thing. The good thing is it doesn't do full tablescans, it will use your foriegn key indexes!

So our rewritten query would look like this:

SELECT c.check_id,(SELECT count(1) FROM responses r WHERE r.check_id = c.check_id)
FROM c.checks

Golden, now for each row it just joins using the index pulling back a single row from responses!

Bookmark to: