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
364
questions
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. ...
- 38.4k
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 ...
- 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?
- 17.6k
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
,...
- 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 ...
- 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 ...
- 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: ...
- 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=...
- 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 ...
- 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)
...
- 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]....
- 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]
( ...
user5426326
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 ...
- 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 ...
- 16.8k
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 (...
- 21.1k
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,
...
- 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,...
- 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 ...
- 5,577
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:
...
- 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 ...
- 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 .....
- 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 ...
- 1.1m
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 ...
- 403
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 (
...
- 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 ...
- 1,564
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:
...
- 703
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 ...
- 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! ...
- 898
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.
...
- 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 ...
- 37k
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 ...
- 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 ...
- 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 ...
- 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()
...
- 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>&...
- 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 .. ...
- 737
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 ...
- 1,672
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 '...
- 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 ('->' ...
- 73
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 ~...
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 ...
- 43
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.
;...
- 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>
&...
- 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 ...
- 185
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 ...
- 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 ...
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
...
- 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 ...
- 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 />...
- 33
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 ( ...
- 7,295