Updated on February 26, 2015
SQL: The Apply Keyword
The other day I learned something about SQL. In particular, MS SQL. I would never call myself a master, not by a long shot–however, I do have a solid grasp of how to put together rather complex queries, so I was surprised to learn of a new keyword that is just as useful as a simple join.
First, a little rant as to why this is necessary in the first place. I do tend to find the language archaic in parts. For instance, we have all seen the error “Column ‘name’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.’ It occurs when we try to select something out of a query where not all columns have been included in a group by or having statement, leading SQL Server to get confused as to what you actually want (explanation for why here). After learning why, it does make perfect sense, but this leads to a whole class of frustrations that would just go away if this was not the case. If only we could bend the rules of logic, right? (For the record, I say the language is archaic for the syntax, not for the logical issue, which is not unique to SQL and is not easily fixed. But syntactically, why not allow the “*” operator in group by, and have it include all the items in the select statement that are not aggregate functions, and do the same for group by? I digress.)
In my frustration to solve this problem for a particularly large query in which I had multiple nested queries each pulling data from the query below it while the inner layers needed the grouping one way while the others needed it another, I discovered a keyword that is not present in MySQL, and is unique to SQL Server. This keyword is “Apply”.
To be clear, this is treading into hackland, but I will say that the performance of using Outer Apply was far better than any other solution we came up with for our particular problem.
The purpose of the function is to allow you to use table functions, which join statements do not allow. This means you can dynamically create a table, organize the data however you’d like, make whichever columns you need accessible, give that table an alias, and then reference the data in the outer query. But the kicker: you can reference tables that already exist in the outer query from within the apply statement. Have a key or value from a table that you need to use within the apply? No problem, just reference it with the alias given in the outer query.
For example, the following code works great:
SELECT OT1.NAME, APPLIEDTABLE.NAME FROM OUTERTABLE1 AS OT1
OUTER APPLY (
SELECT * FROM INNERTABLE1
WHERE OT1.KEY = IT1.KEY
) AS APPLIEDTABLE