Friday, 3 August 2012

Sql Query : Cross Apply Over the Inner Join

//Inner Join
WITH t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT   
  t1.*, t2o.*
FROM    t1
INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn <= 3 //Record fillter after join
SELECT t1.* FROM sys.objects t1
INNER JOIN myFunction(O.name) F ON F.schema_id= O.schema_id  //NOT USE FUNCTION : gettin error in inner join

//Cross Apply
SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY // First fillter record then join is done --Result Incres speed
        (
         SELECT  TOP 3 * FROM    t2
  WHERE   t2.t1_id = t1.id
         ORDER BY
                t2.rank DESC
        ) t2o
SELECT F.* FROM sys.objects O
CROSS APPLY //Not Error
 (
  SELECT * FROM myFunction(O.name) WHERE F.schema_id= O.schema_id

No comments:

Post a Comment