Cross apply and outer apply in sql server — Transcript

Full transcript

1. 0:00this is part 91 of SQL Server tutorial
2. 0:02in this video we'll discuss the cross
3. 0:04apply and outer apply operators in SQL
4. 0:07server with examples for the purpose of
5. 0:10this demo we'll be using these two
6. 0:12tables department and employee here is
7. 0:14the SQL script to create those tables
8. 0:16and populate them with test data I'll
9. 0:18have the script available on my blog in
10. 0:20case you need it Department ID column in
11. 0:23this employee table is a foreign key
12. 0:25referencing the ID column from
13. 0:27Department table now what we want to do
14. 0:30is write a query that's going to
15. 0:31retrieve all the matching rows between
16. 0:33these two tables within our result set
17. 0:36we want Department name from Department
18. 0:38table and employee name gender and
19. 0:41salary from employee table so the result
20. 0:43set should look like this notice we want
21. 0:46only the matching rows between these two
22. 0:49tables now we can very easily achieve
23. 0:51this using an inner join so let's
24. 0:53quickly write that in a join query so we
25. 0:57want to select star
26. 1:01from Department table let's give it an
27. 1:04alas D and we want to innerjoin the
28. 1:08department table with employee table
29. 1:11let's give an alas for this employee
30. 1:12table e and we need to specify the join
31. 1:16condition D do ID equals e do Department
32. 1:21ID and what are the columns that we want
33. 1:23in our result set we want Department
34. 1:26name from the Department table employee
35. 1:29name
36. 1:30employee agenda and employee salary so
37. 1:34if we execute this query we should get
38. 1:36all the matching rules between
39. 1:38department and employee table notice
40. 1:41within the result set we don't have any
41. 1:43rules for administration and sales
42. 1:44departments that's basically because
43. 1:46within the employee table we don't have
44. 1:48any employees belonging to that
45. 1:51departments right so that's the reason
46. 1:53why we don't have those rows in the
47. 1:55result set now let's say we want to
48. 1:57write another query which is going to
49. 1:58retrieve all the matching rows plus
50. 2:00non-matching rows from the Department
51. 2:02table we can very easily achieve that
52. 2:05using a left join instead of an inner
53. 2:08join so when we perform a left join
54. 2:10between department and employee table we
55. 2:12are going to get all the rows from the
56. 2:14Department table irrespective of whether
57. 2:16they have a matching record in the
58. 2:18employee table or not notice we have
59. 2:20Administration and sales departments
60. 2:22here since they don't have any employees
61. 2:24in the employee table the name g and
62. 2:27salary column from the employee table
63. 2:29have n
64. 2:30all right now let's say for some reason
65. 2:32we don't have access to this physical
66. 2:35table employee instead they have given
67. 2:38us a function and that's a table valued
68. 2:40function so if you give the department
69. 2:43ID to that function it's going to give
70. 2:45you the list of all employees belonging
71. 2:48to that department first let's go ahead
72. 2:51and create that function so we want to
73. 2:54create a function let's call it FN
74. 2:57uncore get
75. 3:00employees
76. 3:02by Department ID so we want to pass
77. 3:06Department ID to this function let's
78. 3:08call that department ID and the data
79. 3:12type is integer so this is going to be a
80. 3:14table valued function so this returns a
81. 3:19table
82. 3:21as return and then we'll specify our
83. 3:24query here so select
84. 3:27star from
85. 3:30employee
86. 3:31table
87. 3:33where Department ID equals our input
88. 3:38parameter at Department ID so let's go
89. 3:42ahead and create this
90. 3:45function so we have created the function
91. 3:47now let's quickly test
92. 3:50this select star from the name of our
93. 3:53function is FN get employees by
94. 3:56Department ID so for example if we pass
95. 3:58Department ID one one this function is
96. 4:01going to return us all the employees
97. 4:03within that department so notice
98. 4:06Department ID is one all right now what
99. 4:09we want to do is we want to join the
100. 4:11department table with this table valued
101. 4:14function now to do that can we use an
102. 4:17inner join or a left join if we try to
103. 4:19do that you know we are going to get an
104. 4:21error let's look at that quickly in
105. 4:23action so here on the left we have our
106. 4:25department table now what I want to do
107. 4:28is join that with this function the
108. 4:31table valued
109. 4:33function and if you look at this
110. 4:35function it has got an input parameter
111. 4:38Department ID and I'm going to use this
112. 4:41table name and pass d. ID there and the
113. 4:46alas is still going to be e okay now
114. 4:49look at this when I execute this query
115. 4:51we are going to get an error look at
116. 4:53that it says the multiart identifier d.
117. 4:56ID cannot be bound okay so you cannot do
118. 5:00an inner join like this between a
119. 5:02physical table and a table valued
120. 5:04function so this is where we use the
121. 5:07cross apply and outer apply operators in
122. 5:10SQL Server now I'm simply going to
123. 5:12replace this inner join
124. 5:16with cross apply
125. 5:19operator okay so the cross apply
126. 5:21operator is going to semantically behave
127. 5:24like an inner joint whereas an outer
128. 5:26apply operator is semantically going to
129. 5:28behave like a left out to join so when
130. 5:31we use cross apply we don't require any
131. 5:33join condition because all this function
132. 5:35requires is a department ID and on the
133. 5:39left here we have a table department and
134. 5:41we are passing the department ID so this
135. 5:44function right here gets called for
136. 5:47every row within the department table
137. 5:50okay and the department ID is passed the
138. 5:52employees are retrieved and you know the
139. 5:55department name column is retrieved for
140. 5:57every Row from the Department table
141. 6:00and employee name gender and salary are
142. 6:02retrieved from this function okay so we
143. 6:06don't require this join condition so
144. 6:09let's go ahead and execute this now we
145. 6:12should get the same result only the
146. 6:15matching rows between those two okay
147. 6:18look at this when I execute this I get
148. 6:21you know only it payroll and HR
149. 6:23departments where there are employees
150. 6:26whereas Administration and sales we
151. 6:28don't have that in the results at so if
152. 6:30you want even the non-matching rows from
153. 6:32the Department table then instead of
154. 6:34cross apply use outer apply so let's
155. 6:38make a copy of
156. 6:44this outer apply now when we execute
157. 6:47this we should get the non-matching rows
158. 6:49from Department table as well and notice
159. 6:52the columns from the table valued
160. 6:54function are null so this is
161. 6:56semantically equivalent to um left out
162. 7:00to
163. 7:02join so what do this cross apply and
164. 7:05outer apply operators in SQL Server
165. 7:08these operators are actually introduced
166. 7:09in SQL Server 2005 and they used to join
167. 7:12a table with a table valued function the
168. 7:16table valued function on the right hand
169. 7:17side of the apply operator gets called
170. 7:20for each row from the left table this
171. 7:23left table is also usually called as out
172. 7:25table cross apply returns only matching
173. 7:28row so it's semantically equivalent to
174. 7:30an engine join whereas outer apply
175. 7:33returns matching plus non-matching rows
176. 7:36so this is semantically equivalent to a
177. 7:38left out join the unmatched Columns of
178. 7:40the table valued function will be set to
179. 7:44nulls as we can see here in the result
180. 7:46set so name gener and salary are coming
181. 7:48from the table valued function and since
182. 7:51we don't have you know any result
183. 7:54produced for these two rows you know
184. 7:57from the Department table those Colum s
185. 7:59from the table valued function will be
186. 8:01set to
187. 8:03null so here we have the cross apply
188. 8:06operator example and here we have the
189. 8:09outer apply operator
190. 8:11example thank you for listening and have
191. 8:13a great day