Effects of using Derived Table

Question

Post reply

Effects of using Derived Table

fec

SSC Eights!

Points: 941
More actions
September 23, 2008 at 7:15 pm

#213016

I want to use derived tables instead of temp tables but need to check on a few things first.
Is the data in a derived table filtered by what it is linked to?
Consider the following code:
SELECT *
FROM DProduct AS p
INNER JOIN IItem AS i ON p.DProductID = i.DProduct
INNER JOIN ILabelBatch AS lb ON i.ILabelBatch = lb.ILabelBatchID
INNER JOIN IItemOperationReport AS ior ON i.SerialNumber = ior.SerialNumber
INNER JOIN (
SELECT ior2.SerialNumber
, MIN(ior2.SequenceStarted) AS 'minRunDate'
, ao2.DOperation
FROM IItemOperationReport AS ior2
INNER JOIN DProductStateAllowsOperation AS ao2 ON ior2.DOperation = ao2.DOperation
AND ior2.DProduct = ao2.DProduct
GROUP BY ior2.SerialNumber, ao2.DOperation
) AS FIRSTRUN ON ior.SerialNumber = FIRSTRUN.SerialNumber
AND ior.DOperation = FIRSTRUN.DOperation
AND ior.SequenceStarted = FIRSTRUN.minRunDate
INNER JOIN DOperation AS o ON ior.DOperation = o.DOperationID
WHERE ior.EOperationResultCode = 'PASS'
Will the data in the derived table FIRSTRUN include all serial numbers? Or will it only include those serial numbers which have a 'PASS' result (filtered by the WHERE CLAUSE)?
Sorry if i have confused you...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

fec SSC Eights! Points: 941 More actions · Answer 1

just finished my testing and it appears that the derived table is not filtered by what it is linked to.

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

For your code

the derived table FIRSTRUN will return ALL results, since you didn't put a WHERE condition in there

easiest way: select the actual code inside the ( .... ), then hit F5 to run it

Think of DERIVED TABLE as another table, or view, with no natural sorting nor filter

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Jerry Hung (9/24/2008)
For your code
the derived table FIRSTRUN will return ALL results, since you didn't put a WHERE condition in there
easiest way: select the actual code inside the ( .... ), then hit F5 to run it
Think of DERIVED TABLE as another table, or view, with no natural sorting nor filter

Not so.

The following example uses a standard tally table called Numbers. The execution plan shows that the derived table is filtered on a.number (using the index), returning 11 rows.

[font="Courier New"]SELECT *

FROM Numbers a

INNER JOIN (

SELECT number, CHAR(CAST(SQRT(SQRT(Number)) AS INT)+65) AS Choke1, NEWID() AS Choke2

FROM Numbers) b ON b.number = a.number

WHERE a.number BETWEEN 300000 AND 300010

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden