Cross apply and outer apply in sql server




About this tutorial:

Video duration: 8:16
cross apply sql server example
sql server outer apply example
sql server join table function with parameter
sql server inner join table valued function

In this video we will discuss cross apply and outer apply in sql server with examples.

We want to retrieve all the matching rows between Department and Employee tables.

This can be very easily achieved using an Inner Join as shown below.
Select D.DepartmentName, E.Name, E.Gender, E.Salary
from Department D
Inner Join Employee E
On D.Id = E.DepartmentId

Now if we want to retrieve all the matching rows between Department and Employee…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

33 thoughts on “Cross apply and outer apply in sql server

    dennis daniel

    (November 28, 2018 - 5:00 am)

    How simply he is explaining..Great Sir.Thank you very much.

    Dasari Venkata Chalapathi

    (November 28, 2018 - 5:00 am)

    good explanation

    Nycolas Silvestre

    (November 28, 2018 - 5:00 am)

    Thank you for the explanation!
    I really appreciate it.

    Aarti Bhatia

    (November 28, 2018 - 5:00 am)

    Hello Sir, u r my Guru. Please provide us tutorials for Optimisation. It's a request..

    suryanarayan baliarsingh

    (November 28, 2018 - 5:00 am)

    Brilliant… Once again Thank you Venkat Sir..Your explanations are outstanding.

    Hisham Shaaban

    (November 28, 2018 - 5:00 am)

    Thank you very much

    Klevin Doda

    (November 28, 2018 - 5:00 am)

    Brilliant!

    Janet Shen

    (November 28, 2018 - 5:00 am)

    I was reading one article after another, trying but failing to understand the intended usage of CROSS/OUTER APPLY. This video was super easy to understand. Thank you!!!

    Michael Mauter

    (November 28, 2018 - 5:00 am)

    Nice video, thank you!

    IrrepressibleGuile Mr Irrepressible

    (November 28, 2018 - 5:00 am)

    Great vid, easy to follow and understand.

    Saif Qawasmeh

    (November 28, 2018 - 5:00 am)

    you my friend are genius, thank you for this tutorial

    Sandeep Sirisinagandla

    (November 28, 2018 - 5:00 am)

    Good explanation…!

    Chris B.

    (November 28, 2018 - 5:00 am)

    This video helped clarify APPLYs a lot. Thank you.

    Luigi Zambetti

    (November 28, 2018 - 5:00 am)

    Hello Kudvenkat, just a question: all the videos in this playlist could be enough for preparing to 70-761 certification exam?

    Steven Jirjis

    (November 28, 2018 - 5:00 am)

    You always have the best content! Clear lecture and demo, every time!

    surhios

    (November 28, 2018 - 5:00 am)

    BRILLIANT !! Million Thanks

    Dinesh S

    (November 28, 2018 - 5:00 am)

    This video make my day as useful. Thanks a lot for the simple explanation. Keep it up Bro…

    Sum X

    (November 28, 2018 - 5:00 am)

    Excellent explanation. I like the fact that you demonstrated APPLY by first relating to our prior knowledge of JOINs. Choice of example makes great sense. Tx Kudvenkat.

    epicwhat001

    (November 28, 2018 - 5:00 am)

    Ok. I think outer apply is equivalent to left inner join based on the results not left outer join. Great video but nobody noticed that, Left outer join will return non matching rows from left only but outer apply returned matching rows and non matching rows from the left

    Panda Po

    (November 28, 2018 - 5:00 am)

    Excellent explanation.

    HungryMynd

    (November 28, 2018 - 5:00 am)

    Great tutorial. Thank you!

    Welum Kxyz

    (November 28, 2018 - 5:00 am)

    nice explanation

    Ugochi Onwuliri

    (November 28, 2018 - 5:00 am)

    venkat you're the best. These videos are so rich and well explained. Thank you for taking out time to do this.

    Sharon Cousar

    (November 28, 2018 - 5:00 am)

    Today, for the first time, I came across code in a stored procedure that used outer apply and I had no idea why it was used. So, like everyone else, I googled outer apply and read articles attempting to explain how to use it. No Good. I simply did not understand. But this simple, clear, easy to understand video brought me up to speed on the outer apply and the cross apply. It's so good to understand and come out of the dark about something. Thank you.

    MrTrojis

    (November 28, 2018 - 5:00 am)

    I noticed for the cross apply example if we go from Id = 1 and Id = 2 and so forth continuing in order and if we were to combine the result sets from each of these in order the order we get in the end is not the same as the order in the final result set of the cross apply operation.

    Under the hood is the table valued function we have being passed Id values that are the return values of some optimization sequence generated by Sql server by choosing the optimal execution plan?

    In other words instead of going fn(1),fn(2),fn(3)… is the function being evaluated like: fn(4),fn(1),fn(3)… etc. based on optimization?

    Is it even possible to gain any insight based on the order of the result set what Sql server did to optimize the query?

    If we were to go in order the cross apply result would go mark,john,steve,sara,mary

    Ronak Patel

    (November 28, 2018 - 5:00 am)

    How could you be so precise. It's amazing. Thanks

    younes shaimi

    (November 28, 2018 - 5:00 am)

    great job

    Matt Nielsen

    (November 28, 2018 - 5:00 am)

    Thanks for the video, make understanding this function much easier.

    Krzysztof S

    (November 28, 2018 - 5:00 am)

    Thank you for educating community. This tutorial is the best available on youtube. You are best teacher in the world.

    Paul

    (November 28, 2018 - 5:00 am)

    Microsoft should hire you to write their books. Good work, thanks

    chitranjan soni

    (November 28, 2018 - 5:00 am)

    excellent explanation…over the vedio on youtube.

    Rajen Shah

    (November 28, 2018 - 5:00 am)

    Excellent explanation…

    Jack Willhoite

    (November 28, 2018 - 5:00 am)

    Excellent explanation of an often misunderstood topic. Thank you.

Leave a Reply

Your email address will not be published. Required fields are marked *