Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Machanics of derived table

Posted on 2011-02-22
12
Medium Priority
?
801 Views
Last Modified: 2012-05-11
I would like to understand the machanics of the sql derived table.

Here is an example:


Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID

Does the derived table return every record from the Shops table and then match it to products?

OR

Does the dervie table corrolate with products using the ID column?
0
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 80 total points
ID: 34951411
It scan full table and after fetch matching rows.
0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 960 total points
ID: 34951577
Mr Shawn

In very basic terms, a derived table is a virtual table that’s calculated on the fly from a select statement.

I can give you some good references where you can find all you need ;-)

When to use Virtual tables and when derived
http://techahead.wordpress.com/2007/10/01/sql-derived-tables/

A big difference is :
A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the server is brought down.

Some extra useages ;-)
http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values


regards

poor beggar
0
 

Author Comment

by:Mr_Shaw
ID: 34952663
Hi PatelAlpesh,

I tested the following:

Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)

What happens is for each ID in the where clause the derived table is executed. Therefore in this example it is run 5 times.

I was thinking that the derived table is run once and then the required rows pulled from the results.



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 960 total points
ID: 34952999
hi

while using a derived table the folowing happens.

1, the derived table is generated
2. the join process kicks in and it can use three diferrent option.
   a. nested loop : for each row in table A is scans all rows in table B for a match
       Table A : driving Table , Table B driven Table (the optimizer will choose the smaller table as the   driving so the execution will result with less loops. )

   b. Hash join : The hash join first scans or computes the entire build input and then builds a hash table in memory. Each row is inserted into a hash bucket depending on the hash value computed for the hash key. This build phase is followed by the probe phase. The entire probe input is scanned or computed one row at a time, and for each probe row, the hash key's value is computed, the corresponding hash bucket is scanned, and the matches are produced.

c. The merge join requires that both inputs be sorted on the merge columns, which are defined by the equality (WHERE) clauses of the join predicate

BTW : a goos filter in the query will usually generate a nested loop.
0
 

Author Comment

by:Mr_Shaw
ID: 34953594
>>a good filter in the query will usually generate a nested loop.

Do you mean put the filter in derived table or main table?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34953701
no i dont
i just mean that adding filters to a join process will result most of the time in a nested loop join.

But.
it will be much better performance if you add filters to your inner query (derived table)
couse the join process will have less rows to ivaluate
0
 

Author Comment

by:Mr_Shaw
ID: 34953789
I assume the SQL Optimizer decides on what approach it takes to process derived tables?
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 960 total points
ID: 34954345
yes ofcourse
but you can help it reach the right decission by tuning your query :-)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34957395
>>I tested the following:

Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)
<<
You did?  

Since it does not compile and you do not state whether you want an INNER or OUTER JOIN, I can only guess this is what you meant:
SELECT  *
FROM    Products P
        INNER JOIN (SELECT  ID, Name FROM shops WHERE   country = 'UK') S ON P.ID = S.ID
WHERE   P.ID IN (1, 2, 4, 55, 66)

Open in new window

0
 
LVL 10

Assisted Solution

by:John Claes
John Claes earned 960 total points
ID: 34958866
acperkins::
if not ginving the Join statement SQL changes the , between 2 tables into a Inner join ;-)

ashilo and Mr_Shaw:
The query you're using folows my prevoius post :

The derived tabel is created from the inner select-query
SELECT  ID, Name FROM shops WHERE   country = 'UK'

This table is then joined with Products P
using the INNER JOIN (, is replaced by INNER JOIN )

Then this result will be checked for the given ID's


To optimize this Query By 1 step
loose the filter upon the Joined result and make the join result smaler
SELECT  *
FROM    Products P
INNER JOIN ( SELECT  ID, Name 
             FROM shops 
             WHERE   country = 'UK') S 
ON P.ID = S.ID AND P.ID in (1, 2, 4, 55, 66)

Open in new window


To Optimze this query By 2 steps
Making the derived Table Smaler and so also the join
SELECT  *
FROM    Products P
INNER JOIN ( SELECT  ID, Name 
             FROM shops 
             WHERE   country = 'UK' AND S.ID in (1, 2, 4, 55, 66)) S ON P.ID = S.ID

Open in new window




0
 

Author Closing Comment

by:Mr_Shaw
ID: 34959923
thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34960917
poor_beggar,
>>if not ginving the Join statement SQL changes the , between 2 tables into a Inner join ;-) <<
You had better double check that.  While SQL Server is very smart, what it cannot do is fix a comple error.  Thre is no way in Hell that SQL Server would convert this to an INNER JOIN:
Select * from Products P
(select ID, Name where country = 'UK' From shops) S
ON P.ID = S.ID
Where P.ID IN (1,2,4,55,66)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question