Apply is one of the very useful feature in SQL server and it might be the case that you will realize that you could have used this in one of your requirements, while working on database, if it was not know till now. So let's discuss it.
Apply keyword provides functionality similar to Join but they can be used in situation where it is not possible to use a Join. This keyword can be used in 2 ways:
So let's start by creating two different tables named Category and Product and add some data. As from the name, Category will be a parent table and Product will be a child table. So our dummy data will look like the following:
Now let's come to the use of Cross Apply. As we discussed earlier, Cross apply can be used like an Inner join. So in order to get the data from both the tables, we can apply the Cross apply as:
Easy enough, quite similar to co-related sub-query, for each row of the Category table (outer query), each and every row of the Product table is evaluated (inner query) and if the result matches, the record is kept for the final result set.
The point is that if we already have the Joins, than what is the use of apply. The Apply keyword is mainly intended to be used with Table Value Functions, where the outer query record set is from a table valued function. So let's move the outer query to create a table valued function:
Now, in order to use it, replace the inner query with the function call and see the results.
Great to work with. Happy Querying...!!!
Apply keyword provides functionality similar to Join but they can be used in situation where it is not possible to use a Join. This keyword can be used in 2 ways:
- Cross Apply : This is similar to use of Inner Join.
- Outer Apply : This is similar to use of Left Outer Join
So let's start by creating two different tables named Category and Product and add some data. As from the name, Category will be a parent table and Product will be a child table. So our dummy data will look like the following:
Now let's come to the use of Cross Apply. As we discussed earlier, Cross apply can be used like an Inner join. So in order to get the data from both the tables, we can apply the Cross apply as:
Easy enough, quite similar to co-related sub-query, for each row of the Category table (outer query), each and every row of the Product table is evaluated (inner query) and if the result matches, the record is kept for the final result set.
The point is that if we already have the Joins, than what is the use of apply. The Apply keyword is mainly intended to be used with Table Value Functions, where the outer query record set is from a table valued function. So let's move the outer query to create a table valued function:
Now, in order to use it, replace the inner query with the function call and see the results.
Great to work with. Happy Querying...!!!
