APPLY is used to invoke a table-valued function for each row of an outer query.
A table-valued function returns a result set based on one or more parameters.
Using APPLY, the input of these parameters are the columns of the left referencing table.
CROSS APPLY works like an INNER JOIN.
Unmatched rows between the left table and the table-valued function do'n appear in the result set.
OUTER APPLY is like an OUTER JOIN.
Non-matched rows are still returned in the result set with NULL values in the function results.
14>
15> CREATE TABLE employee(
16> id INTEGER NOT NULL PRIMARY KEY,
17> first_name VARCHAR(10),
18> last_name VARCHAR(10),
19> salary DECIMAL(10,2),
20> start_Date DATETIME,
21> region VARCHAR(10),
22> city VARCHAR(20),
23> managerid INTEGER
24> );
25> GO
1> INSERT INTO employee VALUES (1, 'Jason' , 'Martin', 5890,'2005-03-22','North','Vancouver',3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, 'Alison', 'Mathews',4789,'2003-07-21','South','Utown',4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, 'James' , 'Smith', 6678,'2001-12-01','North','Paris',5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, 'Celia' , 'Rice', 5567,'2006-03-03','South','London',6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, 'Robert', 'Black', 4467,'2004-07-02','East','Newton',7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, 'Linda' , 'Green' , 6456,'2002-05-19','East','Calgary',8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, 'David' , 'Larry', 5345,'2008-03-18','West','New York',9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, 'James' , 'Cat', 4234,'2007-07-17','West','Regina',9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, 'Joan' , 'Act', 6123,'2001-04-16','North','Toronto',10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> CREATE FUNCTION dbo.fn_getByID(@ID int) RETURNS TABLE
5> AS
6> RETURN
7> SELECT first_name
8> FROM employee
9> WHERE ID = @ID
10> GO
1>
2> SELECT w.last_name
3> FROM employee w
4> CROSS APPLY dbo.fn_getByID
5> (w.ID) AS r
6> ORDER BY w.ID;
7> GO
last_name
----------
Martin
Mathews
Smith
Rice
Black
Green
Larry
Cat
Act
(9 rows affected)
1>
2> drop function dbo.fn_getByID;
3> GO
1>
2>
3>
4> drop table employee;
5> GO
| 4.11.APPLY |
| 4.11.1. | Using APPLY to Invoke a Table-Valued Function for Each Row |