May 23, 2016 at 10:24 am
What are the advantage of using cross apply over union all?
May 23, 2016 at 10:34 am
mynkdby (5/23/2016)
What are the advantage of using cross apply over union all?
They're different operators, they both do different things.
If you're talking of unpivoting using cross apply or union all. The advantage is that the table(s) is read once.
May 23, 2016 at 12:41 pm
Actually below query raised this doubt:
Employee -- "Parent table"
Job -- "Child table" fields --> (Job_id, dept etc)
User --- "Child table" fields --> (user_id, loggedon)
Need to find employees currently logged-in users dept.(from job table) and If there is NULL value in logged in users dept. then display the logged in user record alone. Need to find this without using union/union all and in one select statement.
Example:
Select employee.employee_ID, employee.name
from employee
inner join job on
job.employee_ID =
employee.employee_ID
inner join user on
user.user_ID=employee.employee_ID
where ( user.loggedon = 'T')
May 23, 2016 at 1:23 pm
Need to find this without using union/union all and in one select statement.
Why this restriction?
Though, from the requirement, it doesn't sound like it needs a union or an apply. You probably want a left join to the users table and move the logged in condition into the join, that should do the job (guessing a bit since no tables)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2016 at 2:08 pm
GilaMonster (5/23/2016)
Need to find this without using union/union all and in one select statement.
Why this restriction?
Though, from the requirement, it doesn't sound like it needs a union or an apply. You probably want a left join to the users table and move the logged in condition into the join, that should do the job (guessing a bit since no tables)
The restrictions make it sound like homework.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 23, 2016 at 10:47 pm
Yes Its more like an exercise and restriction made me think of using other options available, but not sure how to proceed.
May 24, 2016 at 3:07 am
mynkdby (5/23/2016)
Yes Its more like an exercise and restriction made me think of using other options available, but not sure how to proceed.
Did you try what I suggested?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2016 at 7:04 pm
Thou cross apply and union all combined data set, the are used for different purpose. Cross apply is used when you want to interact with the data at the row level while union all is used to combined two data sets.
May 25, 2016 at 6:21 am
It's not a question of there being an advantage of one over the other, since they do different things. CROSS APPLY is very similar to INNER JOIN, but with the join ON conditions inside the APPLY as WHERE conditions. UNION [ALL] combines result sets end to end, producing a single result set; it's more like an append.
June 4, 2016 at 3:30 pm
dale_berta (5/25/2016)
It's not a question of there being an advantage of one over the other, since they do different things.
Careful now. What you say is true except when you use them to do the same thing. For example, unpivoting. UNION ALL would require a pass through the whole table for every column (unless you get lucky with individual column indexes but would still be a full pass through the index). CROSS APPLY would only require a single pass.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply