SQL Server APPLY operator

If you would love two scoops of yummy SQL Server query goodness, the APPLY operator is for you.

As I've said before, rarely a day goes by when I don't say "D'oh!" in surprise (and embarrassment) at being unaware of a tool or technology that I probably should have known about, but just didn't.

The SQL Server APPLY operator is one of those gems that I should have known about 5 years ago. I really need to start buying those "What's New In SQL Server" books, and actually reading them.

Imagine this Microsoft Dynamics GP scenario: You need to query vendors and include the vendor's PRIMARY address information, as well as their REMIT TO address information. But it's not quite that simple (it never is).

You find that the client has inconsistent vendor address IDs in their database. Sometimes they just use the address ID of "REMIT", sometimes "REMIT TO" and sometimes "REMIT ADDRESS", among others. And naturally, there are some vendors who don't have a separate REMIT TO address at all. In theory, some vendors may have two or more remit addresses, so you have to handle that situation as well--in which case you only want the TOP 1 remit address record.

While trying to figure out how to write such a query, I came across this article that discussed the OUTER APPLY operator.

Think of it as an OUTER JOIN, but without needing the ON keyword and subsequent matching columns from the two tables. It's also a bit like a subquery, but with the flexibility of a JOIN.

In short, it will make your inner SQL geek salivate.

Here is an example query that retrieves basic vendor information, along with the TOP 1 primary and remit to address records, allowing me to reference the fields in my query through a derived table.

Note that I used the OUTER APPLY version, since I want to return the vendor information even if there is no primary or remit to address. If you only want to return records that have a match in your apply operation, you can use CROSS APPLY, which works like an INNER JOIN. (hence the two scoops of yummy goodness)


SELECT
RTRIM(v.VENDORID) AS VENDORID,
RTRIM(v.VENDNAME) AS VENDNAME,
RTRIM(ISNULL(vpa.VNDCNTCT, '')) AS CONTACT,
RTRIM(ISNULL(vpa.ADDRESS1, '')) AS ADDRESS1,
RTRIM(ISNULL(vpa.ADDRESS2, '')) AS ADDRESS2,
RTRIM(ISNULL(vpa.CITY, '')) AS CITY,
RTRIM(ISNULL(vpa.STATE, '')) AS STATE,
RTRIM(ISNULL(vpa.ZIPCODE, '')) AS ZIPCODE,
RTRIM(ISNULL(vra.ADDRESS1, '')) AS REMITADDRESS1,
RTRIM(ISNULL(vra.ADDRESS2, '')) AS REMITADDRESS2,
RTRIM(ISNULL(vra.CITY, '')) AS REMITCITY,
RTRIM(ISNULL(vra.STATE, '')) AS REMITSTATE,
RTRIM(ISNULL(vra.ZIPCODE, '')) AS REMITZIPCODE,
RTRIM(ISNULL(vpa.PHNUMBR1, '')) AS PHONE1,
RTRIM(ISNULL(vpa.PHNUMBR2, '')) AS PHONE2,
RTRIM(ISNULL(vpa.FAXNUMBR, '')) AS FAX
FROM PM00200 v
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%PRIMARY%') AS vpa
OUTER APPLY (SELECT TOP 1 * FROM PM00300 vpa WHERE VENDORID = v.VENDORID AND ADRSCODE LIKE '%REMIT%') AS vra


I told you it was tasty! Bon Appétit!


Here are a few more articles on the topic:

http://msdn.microsoft.com/en-us/library/ms175156.aspx

http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005

http://decipherinfosys.wordpress.com/2007/10/08/apply-operator-in-sql-server-2005/