SQL Server 使用APPLY运算符

从SQL Server 2005开始，提供了APPLY运算符

内部联接的方式实现

SELECT D.deptname, E.empid, E.empname, E.salary
FROM dbo.Departments AS D
         INNER JOIN dbo.Employees AS E ON D.deptid = E.deptid;

右表表达式为子查询

SELECT D.deptname, E.empid, E.empname, E.salary
FROM dbo.Departments AS D
         CROSS APPLY (SELECT empid, empname, salary FROM dbo.Employees WHERE deptid = D.deptid) AS E;

右表表达式为表值函数

CREATE FUNCTION dbo.fn_get(@deptid AS int)
    RETURNS TABLE
        AS
        RETURN
        SELECT empid, empname, salary
        FROM Employees
        WHERE deptid = @deptid;
 
SELECT D.deptname, E.empid, E.empname, E.salary
FROM dbo.Departments AS D
         CROSS APPLY fn_get(D.deptid) AS E;

OUTER APPLY

SELECT D.deptname, E.empid, E.empname, E.salary
FROM dbo.Departments AS D
         OUTER APPLY fn_get(D.deptid) AS E;

附: 建表语句

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
    DROP TABLE dbo.Employees;
IF OBJECT_ID('dbo.Departments', 'U') IS NOT NULL
    DROP TABLE dbo.Departments;
 
CREATE TABLE dbo.Employees
(
    empid   int         NOT NULL,
    deptid  int         NULL,
    empname varchar(25) NOT NULL,
    salary  money       NOT NULL
        CONSTRAINT PK_Employees PRIMARY KEY (empid)
);
CREATE TABLE dbo.Departments
(
    deptid   int         NOT NULL PRIMARY KEY,
    deptname varchar(25) NOT NULL,
);
GO
 
INSERT INTO dbo.Employees
VALUES (1, NULL, 'Nancy', $10000.00),
       (2, 1, 'Andrew', $5000.00),
       (3, 1, 'Janet', $5000.00),
       (4, 1, 'Margaret', $5000.00),
       (5, 2, 'Steven', $2500.00),
       (6, 2, 'Michael', $2500.00),
       (7, 3, 'Robert', $2500.00),
       (8, 3, 'Laura', $2500.00),
       (9, 3, 'Ann', $2500.00),
       (10, 4, 'Ina', $2500.00),
       (11, 7, 'David', $2000.00),
       (12, 7, 'Ron', $2000.00),
       (13, 7, 'Dan', $2000.00),
       (14, 11, 'James', $1500.00);
INSERT INTO dbo.Departments
VALUES (1, 'HR'),
       (2, 'Marketing'),
       (3, 'Finance'),
       (4, 'R&D'),
       (5, 'Training'),
       (6, 'Gardening');