دستور CROSS APPLY در SQL

SELECT
	T1,Id
  T2.Name
FROM
	T1
CROSS JOIN T2;

SELECT
    p.ProductId,
    p.ProductName,
    s.StoreId
FROM
    production.products p
CROSS JOIN sales.stores s
WHERE s.Quantity > 0
ORDER BY
    ProductName;

SELECT * FROM Department D 
CROSS APPLY 
   ( 
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 
GO
 
SELECT * FROM Department D
INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

SELECT * FROM Department D 
OUTER APPLY 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 
GO
 
SELECT * FROM Department D 
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID 

MASTER TABLE

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x


DETAILS TABLE

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

SELECT M.ID,
            M.NAME,
            D.PERIOD,
            D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID,
                         PERIOD,
                         QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

SELECT M.ID,
            M.NAME,
            D.PERIOD,
            D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID,
                         PERIOD,
                         QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

SELECT M.ID,
            M.NAME,
            C.PERIOD,
            C.QTY
FROM MASTER M
CROSS APPLY dbo.MyFunc(M.ID) C

CREATE FUNCTION MyFunc 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

SELECT DISTINCT values AS UsedTags
FROM Post p
CROSS APPLY STRING_SPLIT(p.Tag, ',');

WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

WITH    q AS
        (
        SELECT  *,
                     ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id