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

Filter by
Sorted by
Tagged with
1136 votes
15 answers
989k views

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. ...
Jeff Meatball Yang's user avatar
34 votes
4 answers
58k views

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 ...
user1178399's user avatar
  • 1,068
33 votes
1 answer
31k views

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's user avatar
19 votes
3 answers
72k views

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 ,...
hoz's user avatar
  • 521
16 votes
1 answer
7k views

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 ...
SUMguy's user avatar
  • 1,615
15 votes
2 answers
19k views

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 ...
tamasf's user avatar
  • 1,068
15 votes
4 answers
379 views

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: ...
SMHorus's user avatar
  • 165
14 votes
2 answers
12k views

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: <data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event=...
James L.'s user avatar
  • 9,403
13 votes
3 answers
54k views

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 ...
Pavel Hodek's user avatar
  • 14.5k
12 votes
2 answers
6k views

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) ...
Coding Flow's user avatar
  • 21.7k
9 votes
2 answers
41k views

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]....
BumbleBee's user avatar
  • 10.6k
8 votes
2 answers
9k views

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

I've below Stored Procedure- ALTER PROCEDURE [dbo].[Optimized_GetArticlePostAMP] ( ...
user avatar
8 votes
2 answers
13k views

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 ...
techBeginner's user avatar
  • 3,822
8 votes
1 answer
799 views

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 ...
Paul Williams's user avatar
8 votes
2 answers
918 views

Crazy SQL question: How to do a sort of cross apply with a pivot?

Customer has shortcuts in their data where they have a quantity of records in a field. When I parse them, I need to manufacture records, one for each quantity, incrementing the "identifier" by 7 days (...
Zachary Scott's user avatar
8 votes
6 answers
1k views

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, ...
greener's user avatar
  • 5,019
7 votes
1 answer
9k views

How to unpivot columns using CROSS APPLY in SQL Server 2012

I want to use CROSS APPLY to UNPIVOT multiple columns. The columns CGL, CPL, EO should become Coverage Type, the values for CGL, CPL, EO should go in column Premium, and values for CGLTria,CPLTria,...
Serdia's user avatar
  • 4,318
7 votes
2 answers
2k views

Why does window functions not work in CROSS APPLY?

There is a simple code. I always thought that both outside ROW_NUMBER and the one in CROSS APPLY clause are supposed to generate the same output (in my example I excepct rn = crn). Could you please ...
Dmitrij Kultasev's user avatar
7 votes
2 answers
4k views

Why cross apply is faster than inner join?

I have the following functions: FunctionA - returns Object ID and Detail ID FunctionB - returns Detail ID and Detail Name The following query is used to extract Object ID, Detail ID and Detail Name: ...
gotqn's user avatar
  • 43.2k
6 votes
2 answers
6k views

How to rewrite CROSS APPLY to INNER JOIN to make the view indexed

On a separate thread I got a working example on how to translate my stored proc to a view, that will hold customer names to orders mapping, where orders are comma-separated lists of orders, including ...
kateroh's user avatar
  • 4,410
6 votes
2 answers
1k views

simplify SQL statement by using CTE

I have a query like the following: SELECT A.a, A.b, B.c, (CASE WHEN ... THEN ... ELSE ... END) AS CalculatedValue, B.d FROM dbo.TableA A INNER JOIN dbo.TableB B ON (...) WHERE (CASE WHEN .....
Davide Piras's user avatar
  • 44.3k
6 votes
2 answers
2k views

Is there any difference between CROSS APPLY and OUTER APPLY when creating a cartesian product?

When creating a cartesian product between two tables, is there any difference between CROSS APPLY and OUTER APPLY? This may seem like a silly question given that without a relationship expressed ...
T.J. Crowder's user avatar
6 votes
2 answers
4k views

Join tables by column names, convert string to column name

I have a table which store 1 row per 1 survey. Each survey got about 70 questions, each column present 1 question SurveyID Q1, Q2 Q3 ..... 1 Yes Good Bad ...... I want to pivot this so ...
PeddiePooh's user avatar
6 votes
1 answer
470 views

TSQL Multiple column unpivot with named rows possible?

I know there are several unpivot / cross apply discussions here but I was not able to find any discussion that covers my problem. What I've got so far is the following: SELECT Perc, Salary FROM ( ...
ksauter's user avatar
  • 112
5 votes
3 answers
7k views

SQL Server cross apply not working?

http://sqlfiddle.com/#!3/78273/1 create table emptb1 ( id int, name varchar(20), dept int ) insert into emptb1 values (1,'vish',10); insert into emptb1 values (2,'vish',10); insert into emptb1 ...
Registered User's user avatar
5 votes
2 answers
275 views

TSQL - extend query consisting of cross apply and pivot

this question is based on my Previous Question. I need to extend the query so that I can incorporate two other tables (running on another server instance). In this Fiddle I added those two tables: ...
Tony Clifton's user avatar
5 votes
1 answer
2k views

Convert T-SQL Cross Apply to Oracle

I'm looking to convert this SQL Server (T-SQL) query that uses a cross apply to Oracle 11g. Oracle does not support Cross Apply until 12g, so I have to find a work-around. The idea behind the query is ...
Brett's user avatar
  • 51
5 votes
1 answer
904 views

CROSS APPLY a FREETEXTTABLE

MS SQL Server 2005: table1 has a full text index. I want to run multiple freetexttable searches against it in a single query, but the two attempts i have fail. any help would be appreciated, thanks! ...
user433342's user avatar
4 votes
3 answers
1k views

Using CTE instead of Cursor

I have the following table structure. I just want to update SubId to all the rows where it is null and where the RawLineNumber is ascending by 1 and also the SeqNumber ascending by 1. ...
Ashley John's user avatar
  • 2,435
4 votes
7 answers
156 views

How to select top N salaries for each person?

I have the table user_id salary month 1 100 1 1 150 2 1 200 3 1 180 4 1 140 5 2 10 1 2 40 2 2 20 3 2 15 ...
gstackoverflow's user avatar
4 votes
2 answers
323 views

Guideline for SQL Server APPLY AND JOIN Keyword

I was reading an article on using apply & join keywords. See some SQL where one example uses inner join & other use apply keyword. Here is table pic SELECT E.EMPID, E.NAME, E.DEPTID, D.NAME ...
Thomas's user avatar
  • 34k
4 votes
5 answers
8k views

How can I use Cross Apply to multiple rows?

I have this table : ;WITH cte AS ( SELECT Name='john' , Times=1 UNION ALL SELECT 'paul' ,2 UNION ALL SELECT 'george' , 3 UNION ALL SELECT 'ringo' , 1 ) I want to ...
Royi Namir's user avatar
  • 147k
4 votes
2 answers
6k views

T-SQL CROSS APPLY with GROUP BY

I'm new to CROSS APPLY and trying to understand the mechanics of exactly how it works. Specifically, in doing some tests, I found that including a GROUP BY clause within the CROSS APPLY statement ...
SQLDM's user avatar
  • 59
4 votes
1 answer
2k views

SQL Rank does not work as expected

Im trying to use SQL function Rank() to get a list the top records of several groups. Here is what im tring that does not work : select hc.hId, hc.DpId, hc.Rank from ( select d.hId, DpId, Rank() ...
Tjamat's user avatar
  • 203
4 votes
1 answer
6k views

Extract data matching a condition inside an XML array in SQL server

Considering this simple table id (int), name (varchar), customFields (xml) customFields contains an XML representation of a C# Dictionary. E.g : <dictionary> <item> <key>&...
Johann's user avatar
  • 12.2k
4 votes
1 answer
4k views

OUTER APPLY in BigQuery

I want to apply a column to a list of users where I pull the latest sign in date . Traditionally I would do something like this: SELECT U.* , O.* FROM Users.Users U OUTER APPLY ( SELECT .. FROM .. ...
Erechtheus's user avatar
4 votes
1 answer
6k views

Does CROSS APPLY WHERE clause work before cross apply or after on the results

I am doing a custom fuzzy matching algorithm that we need in one of our inter applications. I am trying to speed it up. When I do a cross apply against a fuzzy function to find suggested matches, I ...
Casey ScriptFu Pharr's user avatar
4 votes
1 answer
2k views

Obtain maximum row_number inside a cross apply

I am having trouble in calculating the maximum of a row_number in my sql case. I will explain it directly on the SQL Fiddle example, as I think it will be faster to understand: SQL Fiddle Columns '...
Alfons's user avatar
  • 147
4 votes
3 answers
7k views

CROSS APPLY issue - filtering a many-to-one relationship

I have found an issue with how MS SQL Server handles CROSS APPLY. The database I'm working with has a pricing system with the following schema: Service -> Price Model <- Price Component ('->' ...
dcembree83's user avatar
4 votes
0 answers
437 views

SQL Server XQuery perfomance (nested loops)

I'm struggling with query performance. I'm having "flat" XML stored as XML in table. And I'm trying to extract all elements and attributes of that XML to some flat table. The issue is that I'm having ~...
Marek Skolimowski's user avatar
4 votes
1 answer
3k views

Convert T-SQL Cross Apply to Redshift

I am converting the following T-SQL statement to Redshift. The purpose of the query is to convert a column in the table with a value containing a comma delimited string with up to 60 values into ...
user3254527's user avatar
4 votes
3 answers
2k views

CROSS APPLY too slow for running total - TSQL

Please see my code below as it is running too slowly with the CROSS APPLY. How can I remove the CROSS APPLY and add something else that will run faster? Please note I am using SQL Server 2008 R2. ;...
Etienne's user avatar
  • 7,101
3 votes
2 answers
19k views

Cross Apply to get child parent value from Xml in SQL Server

I have the following XML: <root> <row value="US"> <col value="00">Jon</col> <col value="01">David</col> <col value="02">Mike</col> &...
Paresh's user avatar
  • 39
3 votes
1 answer
5k views

CROSS APPLY versus UNPIVOT

I found that UNPIVOT is able to automatically exclude fields that have NULL values. However, CROSS APPLY-VALUES method is not able to do that. Does anyone know how to automatically exclude NULL value ...
Casperonian's user avatar
3 votes
4 answers
2k views

MySQL - Get Last Entry by Time (or first if Null)

I have the following SQL Server 2008 query: SELECT T.*,Data.Value FROM [Table] T OUTER APPLY (SELECT TOP 1 E.Value FROM [Table2] E ORDER BY CASE WHEN T.TDateTime >= E.EDateTime then 1 ...
Simon's user avatar
  • 9,367
3 votes
5 answers
716 views

Cross apply boolean values in SQL

I had the table below which I need to create a Category column will store the boolean values as a category. I would like to capture the Categories as a single column and I don't mind having duplicate ...
Christopher Kinyua's user avatar
3 votes
2 answers
925 views

Efficient Cross Apply with a CLR integrated table function

In SQL Server, I have a CLR integration based table valued function, GetArchiveImages. I call it something like this: SELECT ... FROM Items CROSS APPLY GetArchiveImages(Items.ID) AS archiveimages ...
Bryce Wagner's user avatar
  • 2,660
3 votes
4 answers
7k views

Cross Apply - LINQ to Objects

In T-SQL you can use CROSS APPLY to get all possible variations between the table left and right from the statement. Now I have the following situation in C# and I hope there is a way to solve my ...
hwcverwe's user avatar
  • 5,337
3 votes
1 answer
9k views

Extracting XML data in SQL - too many cross apply statements

I have an xml document containing details from a Statement: <Statement> <Id /> <Invoices> <Invoice> <Id /> <Date /> <AmountDue />...
Jacob Danks's user avatar
3 votes
1 answer
1k views

The equivalent of cross apply and select top

I have table (script below): use master go -- -- if db_id ( 'CrossApplyDemo' ) is not null drop database CrossApplyDemo go create database CrossApplyDemo go use CrossApplyDemo go -- if object_id ( ...
testCoder's user avatar
  • 7,295

1
2 3 4 5
8