Stack Overflow
Stack Overflow

Questions tagged [cross-apply]

The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard

358 questions
1120
votes
15 answers

When should I use CROSS APPLY over INNER JOIN?

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…
Jeff Meatball Yang
  • 37,839
  • 27
  • 91
  • 125
31
votes
1 answer

Using cross apply in update statement

Is it possible to use the cross apply clause in the from part of an update statement, in SQL Server 2005?
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
30
votes
4 answers

Postgres analogue to CROSS APPLY in SQL Server

I need to migrate SQL queries written for MS SQL Server 2005 to Postgres 9.1. What is the best way to substitute for CROSS APPLY in this query? SELECT * FROM V_CitizenVersions CROSS APPLY dbo.GetCitizenRecModified(Citizen,…
user1178399
  • 1,028
  • 8
  • 17
  • 32
18
votes
3 answers

CROSS/OUTER APPLY in MySQL

I need to use CROSS APPLY in MySQL (EC2 RDS MySQL instance). Looks like MySQL doesn't recognise the CROSS APPLY Syntax. Can someone help me please? Here's the query. SELECT ORD.ID ,ORD.NAME ,ORD.DATE ,ORD_HIST.VALUE FROM ORD CROSS APPLY…
hoz
  • 502
  • 2
  • 9
  • 25
15
votes
4 answers

Parent count based on pairing of multiple children

In the below example, I'm trying to count the number of drinks I can make based on the availability of ingredients per bar location that I have. To further clarify, as seen in the below example: based on the figures highlighted in the chart below; I…
SMHorus
  • 165
  • 6
15
votes
2 answers

Entity Framework and CROSS/OUTER APPLY

I want to create some test cases for Entity Framework queries that surely generate SQL commands that contain CROSS APPLY or OUTER APPLY operators. Could someone show typical scenarios where these kind of SQL queries appear?
tamasf
  • 1,068
  • 2
  • 10
  • 22
14
votes
2 answers

cross apply xml query performs exponentially worse as xml document grows

What I Have I have a variable size XML document that needs to be parsed on MSSQL 2008 R2 that looks like this:
James L.
  • 9,384
  • 5
  • 38
  • 77
13
votes
1 answer

OPENJSON cross apply with NULL values (TSQL)

I have a series of OPENJSON statements and on the final step of my stored procedure, I parse some JSON from the final column in the second-to-last table. The last column is almost always empty, but it is populated from a JSON object so it will…
SUMguy
  • 1,505
  • 4
  • 31
  • 61
12
votes
2 answers

LINQ to SQL cross apply

I would like to create a query with a cross apply into a user defined table value function in LINQ. The SQL would be really rather simple as below: SELECT * FROM MyTable mt CROSS APPLY MyTVF(mt.id) This post gives an example of a LINQ query that…
Ben Robinson
  • 21,601
  • 5
  • 62
  • 79
12
votes
3 answers

CROSS APPLY with table valued function restriction performance

I have problem with CROSS APPLY with parametrised table valued function. Here is simplified pseudo code example: SELECT * FROM ( SELECT lor.* FROM LOT_OF_ROWS_TABLE lor WHERE ... ) AS lor CROSS APPLY dbo.HeavyTableValuedFunction(lor.ID)…
Pavel Hodek
  • 14,319
  • 3
  • 32
  • 37
9
votes
2 answers

Get the count of nodes in an XML field XQuery SQL Server 2008

I am trying to get the count of nodes in an XML field. but I always see 0 as the result. Here is how my query looks like. DECLARE @XmlTable TABLE (XmlResult XML) INSERT INTO @XmlTable EXECUTE [dbo].usp_GetBooks @EditionId=400 --select * from …
BumbleBee
  • 10,429
  • 20
  • 78
  • 123
8
votes
2 answers

when choose CROSS APPLY and when EXISTS?

I read, CROSS APPLY is just like JOIN.. and I think JOIN can be accomplished with EXISTS also (correlated sub query) I am confused, what is the difference in using CROSS APPLY and EXISTS? when should I go for CROSS APPLY against EXISTS?
techBeginner
  • 3,792
  • 11
  • 43
  • 59
8
votes
1 answer

Why does CROSS APPLY *not* get an invalid column error in this query?

I am writing some code to query some DMVs. Some of the columns may or may not exist in the DMV depending on SQL version. I found an interesting suggestion online how to skip specific checking using CROSS APPLY. The query below is an example of…
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
8
votes
2 answers

How to handle empty rows in CROSS APPLY [SQL Server]

I've below Stored Procedure- ALTER PROCEDURE [dbo].[Optimized_GetArticlePostAMP] ( @PostID int …
user5426326
8
votes
6 answers

Sequential SQL inserts when triggered by CROSS APPLY

This process has several steps which are reflected in various tables of a database: Production --> UPDATE to the inventory table using something like UPDATE STOR SET STOR.BLOC1 = T.BLOC1, STOR.BLOC2 = T.BLOC2, STOR.BLOC3 = T.BLOC3, …
greener
  • 4,989
  • 13
  • 52
  • 93
1
2 3
…
23 24