Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i am attempting to run a subquery in a table:

SELECT t1.*, t2.* FROM t1 
LEFT JOIN (SELECT * FROM table2 GROUP BY col1) AS t2 on t1.col1 = t2.col1
WHERE ...

My main question is, since that subquery called on it's own would pull the entire time, does it also pull the entire time while it's in a subquery, even though the WHERE condition on the parent query is only pulling just a few rows of the actual database?

I suppose I could put the where query inside the subquery, but its a pretty complex where query that involves other tables.

share|improve this question
1  
You could do an EXPLAIN to see what's happening "under the hood". Edit: you don't mention which DBMS you're using, so I can't link to any documentation about how your DBMS uses EXPLAIN. –  Petter Brodin Jan 23 '13 at 19:10
2  
Hi, what database engine are you using? I don't think there's a general answer to your question, different engines have different capabilities in terms of refactoring/predicate pushdown/... Also the explain plan (or equivalent) for your real query might shed some light. –  Mat Jan 23 '13 at 19:11
    
Technically that's not a sub-query but a derived table. –  a_horse_with_no_name Jan 23 '13 at 23:16

1 Answer 1

It shouldn't. It depends on what the system can simplify the query to.

You can demonstrate this very easily by considering the plan for queries such as:

SELECT *
FROM YourTable
WHERE ID < 5;

SELECT *
FROM (SELECT * FROM YourTable) as t
WHERE ID < 5;

The two should be identical.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.