5

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.

dab
  • 51
  • 1
  • 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. –  Jan 23 '13 at 23:16

1 Answers1

3

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.

Rob Farley
  • 16,199
  • 2
  • 38
  • 61