从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');
文章来源: 锋利的SQL(第2版)7.8使用APPLY运算符