When should I use CROSS APPLY over INNER JOIN? – This article will take you through the common SQL errors that you might encounter while working with sql, sql-server, performance. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
What is the main purpose of using CROSS APPLY?
I have read (vaguely, through posts on the Internet) that
cross apply can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)
I also know that
CROSS APPLY doesn’t require a UDF as the right-table.
INNER JOIN queries (one-to-many relationships), I could rewrite them to use
CROSS APPLY, but they always give me equivalent execution plans.
Can anyone give me a good example of when
CROSS APPLY makes a difference in those cases where
INNER JOIN will work as well?
Here’s a trivial example, where the execution plans are exactly the same. (Show me one where they differ and where
cross apply is faster/more efficient)
create table Company ( companyId int identity(1,1) , companyName varchar(100) , zipcode varchar(10) , constraint PK_Company primary key (companyId) ) GO create table Person ( personId int identity(1,1) , personName varchar(100) , companyId int , constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId) , constraint PK_Person primary key (personId) ) GO insert Company select 'ABC Company', '19808' union select 'XYZ Company', '08534' union select '123 Company', '10016' insert Person select 'Alan', 1 union select 'Bobby', 1 union select 'Chris', 1 union select 'Xavier', 2 union select 'Yoshi', 2 union select 'Zambrano', 2 union select 'Player 1', 3 union select 'Player 2', 3 union select 'Player 3', 3 /* using CROSS APPLY */ select * from Person p cross apply ( select * from Company c where p.companyid = c.companyId ) Czip /* the equivalent query using INNER JOIN */ select * from Person p inner join Company c on p.companyid = c.companyId
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?
See the article in my blog for detailed performance comparison:
CROSS APPLY works better on things that have no simple
This one selects
3 last records from
t2 for each record from
SELECT t1.*, t2o.* FROM t1 CROSS APPLY ( SELECT TOP 3 * FROM t2 WHERE t2.t1_id = t1.id ORDER BY t2.rank DESC ) t2o
It cannot be easily formulated with an
INNER JOIN condition.
You could probably do something like that using
CTE‘s and window function:
WITH t2o AS ( SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn FROM t2 ) SELECT t1.*, t2o.* FROM t1 INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn <= 3
, but this is less readable and probably less efficient.
master is a table of about
20,000,000 records with a
PRIMARY KEY on
WITH q AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM master ), t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t JOIN q ON q.rn <= t.id
runs for almost
30 seconds, while this one:
WITH t AS ( SELECT 1 AS id UNION ALL SELECT 2 ) SELECT * FROM t CROSS APPLY ( SELECT TOP (t.id) m.* FROM master m ORDER BY id ) q
Consider you have two tables.
x------x--------------------x | Id | Name | x------x--------------------x | 1 | A | | 2 | B | | 3 | C | x------x--------------------x
x------x--------------------x-------x | Id | PERIOD | QTY | x------x--------------------x-------x | 1 | 2014-01-13 | 10 | | 1 | 2014-01-11 | 15 | | 1 | 2014-01-12 | 20 | | 2 | 2014-01-06 | 30 | | 2 | 2014-01-08 | 40 | x------x--------------------x-------x
There are many situations where we need to replace
INNER JOIN with
1. Join two tables based on
TOP n results
Consider if we need to select
Master and last two dates for each
SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M INNER JOIN ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D ORDER BY CAST(PERIOD AS DATE)DESC )D ON M.ID=D.ID
The above query generates the following result.
x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | x------x---------x--------------x-------x
See, it generated results for last two dates with last two date’s
Id and then joined these records only in the outer query on
Id, which is wrong. This should be returning both
Ids 1 and 2 but it returned only 1 because 1 has the last two dates. To accomplish this, we need to use
SELECT M.ID,M.NAME,D.PERIOD,D.QTY FROM MASTER M CROSS APPLY ( SELECT TOP 2 ID, PERIOD,QTY FROM DETAILS D WHERE M.ID=D.ID ORDER BY CAST(PERIOD AS DATE)DESC )D
and forms the following result.
x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-08 | 40 | | 2 | B | 2014-01-06 | 30 | x------x---------x--------------x-------x
Here’s how it works. The query inside
CROSS APPLY can reference the outer table, where
INNER JOIN cannot do this (it throws compile error). When finding the last two dates, joining is done inside
CROSS APPLY i.e.,
2. When we need
INNER JOIN functionality using functions.
CROSS APPLY can be used as a replacement with
INNER JOIN when we need to get result from
Master table and a
SELECT M.ID,M.NAME,C.PERIOD,C.QTY FROM MASTER M CROSS APPLY dbo.FnGetQty(M.ID) C
And here is the function
CREATE FUNCTION FnGetQty ( @Id INT ) RETURNS TABLE AS RETURN ( SELECT ID,PERIOD,QTY FROM DETAILS WHERE ID=@Id )
which generated the following result
x------x---------x--------------x-------x | Id | Name | PERIOD | QTY | x------x---------x--------------x-------x | 1 | A | 2014-01-13 | 10 | | 1 | A | 2014-01-11 | 15 | | 1 | A | 2014-01-12 | 20 | | 2 | B | 2014-01-06 | 30 | | 2 | B | 2014-01-08 | 40 | x------x---------x--------------x-------x
ADDITIONAL ADVANTAGE OF CROSS APPLY
APPLY can be used as a replacement for
CROSS APPLY or
OUTER APPLY can be used here, which are interchangeable.
Consider you have the below table(named
x------x-------------x--------------x | Id | FROMDATE | TODATE | x------x-------------x--------------x | 1 | 2014-01-11 | 2014-01-13 | | 1 | 2014-02-23 | 2014-02-27 | | 2 | 2014-05-06 | 2014-05-30 | | 3 | NULL | NULL | x------x-------------x--------------x
The query is below.
SELECT DISTINCT ID,DATES FROM MYTABLE CROSS APPLY(VALUES (FROMDATE),(TODATE)) COLUMNNAMES(DATES)
which brings you the result
x------x-------------x | Id | DATES | x------x-------------x | 1 | 2014-01-11 | | 1 | 2014-01-13 | | 1 | 2014-02-23 | | 1 | 2014-02-27 | | 2 | 2014-05-06 | | 2 | 2014-05-30 | | 3 | NULL | x------x-------------x
cross apply sometimes enables you to do things that you cannot do with
Example (a syntax error):
select F.* from sys.objects O inner join dbo.myTableFun(O.name) F on F.schema_id= O.schema_id
This is a syntax error, because, when used with
inner join, table functions can only take variables or constants as parameters. (I.e., the table function parameter cannot depend on another table’s column.)
select F.* from sys.objects O cross apply ( select * from dbo.myTableFun(O.name) ) F where F.schema_id= O.schema_id
This is legal.
Or alternatively, shorter syntax: (by ErikE)
select F.* from sys.objects O cross apply dbo.myTableFun(O.name) F where F.schema_id= O.schema_id
Informix 12.10 xC2+ has Lateral Derived Tables and Postgresql (9.3+) has Lateral Subqueries which can be used to a similar effect.
It seems to me that CROSS APPLY can fill a certain gap when working with calculated fields in complex/nested queries, and make them simpler and more readable.
Simple example: you have a DoB and you want to present multiple age-related fields that will also rely on other data sources (such as employment), like Age, AgeGroup, AgeAtHiring, MinimumRetirementDate, etc. for use in your end-user application (Excel PivotTables, for example).
Options are limited and rarely elegant:
JOIN subqueries cannot introduce new values in the dataset based on data in the parent query (it must stand on its own).
UDFs are neat, but slow as they tend to prevent parallel operations. And being a separate entity can be a good (less code) or a bad (where is the code) thing.
Junction tables. Sometimes they can work, but soon enough you’re joining subqueries with tons of UNIONs. Big mess.
Create yet another single-purpose view, assuming your calculations don’t require data obtained mid-way through your main query.
Intermediary tables. Yes… that usually works, and often a good option as they can be indexed and fast, but performance can also drop due to to UPDATE statements not being parallel and not allowing to cascade formulas (reuse results) to update several fields within the same statement. And sometimes you’d just prefer to do things in one pass.
Nesting queries. Yes at any point you can put parenthesis on your entire query and use it as a subquery upon which you can manipulate source data and calculated fields alike. But you can only do this so much before it gets ugly. Very ugly.
Repeating code. What is the greatest value of 3 long (CASE…ELSE…END) statements? That’s gonna be readable!
- Tell your clients to calculate the damn things themselves.
Did I miss something? Probably, so feel free to comment. But hey, CROSS APPLY is like a godsend in such situations: you just add a simple
CROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl and voilà! Your new field is now ready for use practically like it had always been there in your source data.
Values introduced through CROSS APPLY can…
- be used to create one or multiple calculated fields without adding performance, complexity or readability issues to the mix
- like with JOINs, several subsequent CROSS APPLY statements can refer to themselves:
CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
- you can use values introduced by a CROSS APPLY in subsequent JOIN conditions
- As a bonus, there’s the Table-valued function aspect
Dang, there’s nothing they can’t do!
This has already been answered very well technically, but let me give a concrete example of how it’s extremely useful:
Lets say you have two tables, Customer and Order. Customers have many Orders.
I want to create a view that gives me details about customers, and the most recent order they’ve made. With just JOINS, this would require some self-joins and aggregation which isn’t pretty. But with Cross Apply, its super easy:
SELECT * FROM Customer CROSS APPLY ( SELECT TOP 1 * FROM Order WHERE Order.CustomerId = Customer.CustomerId ORDER BY OrderDate DESC ) T
Cross apply works well with an XML field as well. If you wish to select node values in combination with other fields.
For example, if you have a table containing some xml
<root> <subnode1> <some_node value="1" /> <some_node value="2" /> <some_node value="3" /> <some_node value="4" /> </subnode1> </root>
Using the query
SELECT id as [xt_id] ,xmlfield.value('(/root/@attribute)', 'varchar(50)') root_attribute_value ,node_attribute_value = [some_node].value('@value', 'int') ,lt.lt_name FROM dbo.table_with_xml xt CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node]) LEFT OUTER JOIN dbo.lookup_table lt ON [some_node].value('@value', 'int') = lt.lt_id
Will return a result
xt_id root_attribute_value node_attribute_value lt_name ---------------------------------------------------------------------- 1 test1 1 Benefits 1 test1 4 FINRPTCOMPANY
Cross apply can be used to replace subquery’s where you need a column of the subquery
select * from person p where p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')
here i won’t be able to select the columns of company table
so, using cross apply
select P.*,T.CompanyName from Person p cross apply ( select * from Company C where p.companyid = c.companyId and c.CompanyName like '%yyy%' ) T
Here’s a brief tutorial that can be saved in a
.sql file and executed in SSMS that I wrote for myself to quickly refresh my memory on how
CROSS APPLY works and when to use it:
-- Here's the key to understanding CROSS APPLY: despite the totally different name, think of it as being like an advanced 'basic join'. -- A 'basic join' gives the Cartesian product of the rows in the tables on both sides of the join: all rows on the left joined with all rows on the right. -- The formal name of this join in SQL is a CROSS JOIN. You now start to understand why they named the operator CROSS APPLY. -- Given the following (very) simple tables and data: CREATE TABLE #TempStrings ([SomeString] [nvarchar](10) NOT NULL); CREATE TABLE #TempNumbers ([SomeNumber] [int] NOT NULL); CREATE TABLE #TempNumbers2 ([SomeNumber] [int] NOT NULL); INSERT INTO #TempStrings VALUES ('111'); INSERT INTO #TempStrings VALUES ('222'); INSERT INTO #TempNumbers VALUES (111); INSERT INTO #TempNumbers VALUES (222); INSERT INTO #TempNumbers2 VALUES (111); INSERT INTO #TempNumbers2 VALUES (222); INSERT INTO #TempNumbers2 VALUES (222); -- Basic join is like CROSS APPLY; 2 rows on each side gives us an output of 4 rows, but 2 rows on the left and 0 on the right gives us an output of 0 rows: SELECT st.SomeString, nbr.SomeNumber FROM -- Basic join ('CROSS JOIN') #TempStrings st, #TempNumbers nbr -- Note: this also works: --#TempStrings st CROSS JOIN #TempNumbers nbr -- Basic join can be used to achieve the functionality of INNER JOIN by first generating all row combinations and then whittling them down with a WHERE clause: SELECT st.SomeString, nbr.SomeNumber FROM -- Basic join ('CROSS JOIN') #TempStrings st, #TempNumbers nbr WHERE st.SomeString = nbr.SomeNumber -- However, for increased readability, the SQL standard introduced the INNER JOIN ... ON syntax for increased clarity; it brings the columns that two tables are -- being joined on next to the JOIN clause, rather than having them later on in the WHERE clause. When multiple tables are being joined together, this makes it -- much easier to read which columns are being joined on which tables; but make no mistake, the following syntax is *semantically identical* to the above syntax: SELECT st.SomeString, nbr.SomeNumber FROM -- Inner join #TempStrings st INNER JOIN #TempNumbers nbr ON st.SomeString = nbr.SomeNumber -- Because CROSS APPLY is generally used with a subquery, the subquery's WHERE clause will appear next to the join clause (CROSS APPLY), much like the aforementioned -- 'ON' keyword appears next to the INNER JOIN clause. In this sense, then, CROSS APPLY combined with a subquery that has a WHERE clause is like an INNER JOIN with -- an ON keyword, but more powerful because it can be used with subqueries (or table-valued functions, where said WHERE clause can be hidden inside the function). SELECT st.SomeString, nbr.SomeNumber FROM #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr -- CROSS APPLY joins in the same way as a CROSS JOIN, but what is joined can be a subquery or table-valued function. You'll still get 0 rows of output if -- there are 0 rows on either side, and in this sense it's like an INNER JOIN: SELECT st.SomeString, nbr.SomeNumber FROM #TempStrings st CROSS APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr -- OUTER APPLY is like CROSS APPLY, except that if one side of the join has 0 rows, you'll get the values of the side that has rows, with NULL values for -- the other side's columns. In this sense it's like a FULL OUTER JOIN: SELECT st.SomeString, nbr.SomeNumber FROM #TempStrings st OUTER APPLY (SELECT * FROM #TempNumbers tempNbr WHERE 1 = 2) nbr -- One thing CROSS APPLY makes it easy to do is to use a subquery where you would usually have to use GROUP BY with aggregate functions in the SELECT list. -- In the following example, we can get an aggregate of string values from a second table based on matching one of its columns with a value from the first -- table - something that would have had to be done in the ON clause of the LEFT JOIN - but because we're now using a subquery thanks to CROSS APPLY, we -- don't need to worry about GROUP BY in the main query and so we don't have to put all the SELECT values inside an aggregate function like MIN(). SELECT st.SomeString, nbr.SomeNumbers FROM #TempStrings st CROSS APPLY (SELECT SomeNumbers = STRING_AGG(tempNbr.SomeNumber, ', ') FROM #TempNumbers2 tempNbr WHERE st.SomeString = tempNbr.SomeNumber) nbr -- ^ First the subquery is whittled down with the WHERE clause, then the aggregate function is applied with no GROUP BY clause; this means all rows are -- grouped into one, and the aggregate function aggregates them all, in this case building a comma-delimited string containing their values. DROP TABLE #TempStrings; DROP TABLE #TempNumbers; DROP TABLE #TempNumbers2;
I guess it should be readability 😉
CROSS APPLY will be somewhat unique for people reading to tell them that a UDF is being used which will be applied to each row from the table on the left.
Ofcourse, there are other limitations where a CROSS APPLY is better used than JOIN which other friends have posted above.
Here is an article that explains it all, with their performance difference and usage over JOINS.
SQL Server CROSS APPLY and OUTER APPLY over JOINS
As suggested in this article, there is no performance difference between them for normal join operations (INNER AND CROSS).
The usage difference arrives when you have to do a query like this:
CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT) RETURNS TABLE AS RETURN ( SELECT * FROM Employee E WHERE E.DepartmentID = @DeptID ) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
That is, when you have to relate with function. This cannot be done using INNER JOIN, which would give you the error “The multi-part identifier “D.DepartmentID” could not be bound.” Here the value is passed to the function as each row is read. Sounds cool to me. 🙂
Well I am not sure if this qualifies as a reason to use Cross Apply versus Inner Join, but this query was answered for me in a Forum Post using Cross Apply, so I am not sure if there is an equalivent method using Inner Join:
Create PROCEDURE [dbo].[Message_FindHighestMatches] -- Declare the Topical Neighborhood @TopicalNeighborhood nchar(255)
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON Create table #temp ( MessageID int, Subjects nchar(255), SubjectsCount int ) Insert into #temp Select MessageID, Subjects, SubjectsCount From Message Select Top 20 MessageID, Subjects, SubjectsCount, (t.cnt * 100)/t3.inputvalues as MatchPercentage From #temp cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1 join dbo.Split(@TopicalNeighborhood,',') as t2 on t1.value = t2.value) as t cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3 Order By MatchPercentage desc drop table #temp
This is perhaps an old question, but I still love the power of CROSS APPLY to simplify the re-use of logic and to provide a “chaining” mechanism for results.
I’ve provided a SQL Fiddle below which shows a simple example of how you can use CROSS APPLY to perform complex logical operations on your data set without things getting at all messy. It’s not hard to extrapolate from here more complex calculations.
While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.
Stolen from Here
We use CROSS APPLY to update a table with JSON from another (update request) table — joins won’t work for this as we use OPENJSON, to read the content of the JSON, and OPENJSON is a “table-valued function”.
I was going to put a simplified version of one of our UPDATE commands here as a example but, even simplified, it is rather large and overly complex for an example. So this much simplied “sketch” of just part of the command will have to suffice:
SELECT r.UserRequestId, j.xxxx AS xxxx, FROM RequestTable as r WITH (NOLOCK) CROSS APPLY OPENJSON(r.JSON, '$.requesttype.recordtype') WITH( r.userrequestid nvarchar(50) '$.userrequestid', j.xxx nvarchar(20) '$.xxx )j WHERE r.Id > @MaxRequestId and ... etc. ....
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.