Machanics of derived table

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?
Mr_ShawAsked:
Who is Participating?
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
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
 
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
It scan full table and after fetch matching rows.
0
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Mr_ShawAuthor Commented:
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
 
Mr_ShawAuthor Commented:
>>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
 
Aaron ShiloChief Database ArchitectCommented:
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
 
Mr_ShawAuthor Commented:
I assume the SQL Optimizer decides on what approach it takes to process derived tables?
0
 
Aaron ShiloConnect With a Mentor Chief Database ArchitectCommented:
yes ofcourse
but you can help it reach the right decission by tuning your query :-)
0
 
Anthony PerkinsCommented:
>>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
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
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
 
Mr_ShawAuthor Commented:
thanks.
0
 
Anthony PerkinsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.